Postgresql function

语法

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

简单的Postgresql RETURN NEXT 应用

PostgreSQL的存储过程简单入门