语法
CREATE [ OR REPLACE ] FUNCTION
name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
[ RETURNS rettype
| RETURNS TABLE ( column_name column_type [, ...] ) ]
{ LANGUAGE lang_name
| WINDOW
| IMMUTABLE | STABLE | VOLATILE | [ NOT ] LEAKPROOF
| CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
| [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
| COST execution_cost
| ROWS result_rows
| SET configuration_parameter { TO value | = value | FROM CURRENT }
| AS 'definition'
| AS 'obj_file', 'link_symbol'
} ...
[ WITH ( attribute [, ...] ) ]
注:中括号[]表示可选,有没有都行;大括号{ | }表示必选且只能选一个,选项用|分隔;省略号...表示重复前面的项;小括号()跟前面三个不一样,这个就是函数后面的括号。
其中:
name 为函数名,可以带上schema进行限定;
argmode 为参数模式,in out inout或VARIADIC;argname就是参数名字;argtype是参数类型。值得注意的是,argmode和argname都是可以省略的,argmod省略后默认是IN,argname省略后用$1,$2去获取;
rettype 返回值类型,这个跟out作用相似,两者在使用的时候会有一定冲突,需要注意;
language lang_name 包括SQL、plpgsql等,不同的语言决定了函数体的写法;
as 后就可以跟函数体了。
另外,在些函数体开始和结束的时候一般会看到$ $符号,所有被$ $包起来的字符串都是常量字符串,其中的任何字符都不需要转义,包括$本身,另外$符号中间还可以加tag,如$body$,这个tag的目的是为了识别字符串常量的开始和结束,这样也方便进行常量字符串的嵌套。
例子
CREATE FUNCTION dup(in int, out f1 int, out f2 text)
AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
LANGUAGE SQL;
SELECT * FROM dup(42);
注:||是字符串连接符
CREATE FUNCTION check_password(uname TEXT, pass TEXT)
RETURNS BOOLEAN AS $$
DECLARE passed BOOLEAN;
BEGIN
SELECT (pwd = $2) INTO passed
FROM pwds
WHERE username = $1;
RETURN passed;
END;
$$ LANGUAGE plpgsql
SECURITY DEFINER
-- Set a secure search_path: trusted schema(s), then 'pg_temp'.
SET search_path = admin, pg_temp;
注:
1、plpsql的函数体的基本结构是declare --声明表量(不声明不能用)-- begin --函数体-- end
2、需要有return
create or replace function demo(IN startDate date, IN endDate date) returns setof date as
$body$
declare
days int;
d int;
wd workdays%rowtype;
begin
select (endDate - startDate) into days;
for d in 0..days loop
execute 'insert into workdays select ('''||startDate||'''::date+'||d||')';
end loop;
for wd in select * from workdays loop
return next wd;
end loop;
end
$body$
language plpgsql;
select * from demo('20141220','20141225');
CREATE OR REPLACE FUNCTION insert_new_item()
RETURNS trigger AS
$BODY$
declare
maxorder int;
count int;
begin
select count(1) from demo into count;
if(count = 0) then
NEW.torder := 0;
else
select max(torder) from demo into maxorder;
NEW.torder := maxorder + 1;
end if;
return NEW;
end
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION insert_new_item()
OWNER TO postgres;
CREATE OR REPLACE FUNCTION test_func()
RETURNS TABLE(lid character varying, mid character varying) AS
$BODY$
begin
return query select layerid,mapid from map_layers;
end;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000;
ALTER FUNCTION test_func()
OWNER TO postgres;
参考
PostgreSQL 9.3.5 Documentation:CREATE FUNCTION