https://github.com/pgxn/pgxn-manager/tree/master/sql
这里是一个简单的插入语句包装器,用于说明:CREATE OR REPLACE FUNCTION insert_user(
nickname LABEL,
password TEXT,
full_name TEXT DEFAULT '',
email EMAIL DEFAULT NULL,
uri URI DEFAULT NULL,
twitter CITEXT DEFAULT NULL,
why TEXT DEFAULT NULL
) RETURNS BOOLEAN LANGUAGE plpgsql SECURITY DEFINER AS $$
/*
% SELECT insert_user(
nickname := 'theory',
password := '***',
full_name := 'David Wheeler',
email := 'theory@pgxn.org',
uri := 'http://justatheory.com/',
twitter := 'theory',
why := 'Because I’m a bitchin’ Pg developer, yo.'
);
insert_user
─────────────
t
Inserts a new user into the database. The nickname must not already exist or
an exception will be thrown. The password must be at least four characters
long or an exception will be thrown. The status will be set to "new" and the
`set_by` set to the new user's nickname. The other parameters are:
full_name
: The full name of the user.
email
: The email address of the user. Must be a valid email address as verified by
[Email::Valid](http://search.cpan.org/perldoc?Email::Valid).
uri
: Optional URI for the user. Should be a valid URI as verified by
[Data::Validate::URI](http://search.cpan.org/perldoc?Data::Validate::URI).
twitter
: Optional Twitter username. Case-insensitive. A leading "@" will be removed.
why
: Optional text from the user explaining why she should be allowed access.
Returns true if the user was inserted, and false if not.
*/
BEGIN
IF char_length(password) < 4 THEN
RAISE EXCEPTION 'Password must be at least four characters long';
END IF;
INSERT INTO users (
nickname,
password,
full_name,
email,
uri,
twitter,
why,
set_by
)
VALUES (
insert_user.nickname,
crypt(insert_user.password, gen_salt('des')),
COALESCE(insert_user.full_name, ''),
insert_user.email,
insert_user.uri,
COALESCE(trim(leading '@' FROM insert_user.twitter), ''),
COALESCE(insert_user.why, ''),
insert_user.nickname
);
RETURN FOUND;
END;
$$;
关于SQL风格指南,特别是Postgres SQL方面,我目前不知道有任何官方的指南。如果你的主要目标是代码美化(即编写特定的方式使其更易读/让眼睛舒服),那么你可以选择以下选项:
- 尝试从sourceforge获取pgFormatter(PostgreSQL SQL语法美化程序)
- 从Oracle网站免费获取Oracle SQL Developer工具(有格式化SQL的选项)
- 尝试使用Dell软件提供的第三方程序,如SQL Navigator或TOAD(试用版/付费版)
- 使用带有SQL代码格式化选项的免费编辑器Notepad++
抱歉,对于你的第二个问题,我尚不清楚。