Postgresql函数总结

在PG中，函数在别的数据库里可能也被称为存储过程，他支持四种语言可用：PL/PGSQL,PL/TCL,PL/PERL,PL/PYTHON.
它有着固定的语法格式和前缀后缀：
http://www.phpx.com/man/Pgsql/plpgsql.html

例子如下：
CREATE OR REPLACE FUNCTION nvsdm.fn_fiscal_year(IN quarter_num integer)
  RETURNS TABLE(fiscal_year integer, quarter integer, start_date timestamp with time zone, end_date timestamp with time zone) AS
$BODY$
DECLARE num integer;
DECLARE current DATE;
DECLARE year integer;
DECLARE month integer;
DECLARE fiscal_year integer;
DECLARE quarter integer;
BEGIN
。
。
。
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION nvsdm.fn_fiscal_year(integer)
  OWNER TO sys_sdm;

1.如果函数的返回值是一个TABLE,则函数中必须有RETURN QUERY SELECT ...的写法，用来返回值。如
RETURN QUERY SELECT fiscal_year,quarter,current::timestamp with time zone,(current+INTERVAL '3 month'-INTERVAL '1 second')::timestamp with time zone;
如果返回值是一个TEST，则除了定义的时候要注意
declare rs text;
在返回值是应该为
return rs;

2.注意函数体中简单循环的写法，其中较为常见的为
whiel{expression}  Loop
..

End Loop

贴上一个可以根据大小返回季度起始的函数
-- Function: nvsdm.fn_fiscal_year(integer)


-- DROP FUNCTION nvsdm.fn_fiscal_year(integer);


CREATE OR REPLACE FUNCTION nvsdm.fn_fiscal_year(IN quarter_num integer)
  RETURNS TABLE(fiscal_year integer, quarter integer, start_date timestamp with time zone, end_date timestamp with time zone) AS
$BODY$
DECLARE num integer;
DECLARE current DATE;
DECLARE year integer;
DECLARE month integer;
DECLARE fiscal_year integer;
DECLARE quarter integer;
BEGIN
num := quarter_num;
current := NOW();
year := date_part('year', current)::integer;
month := date_part('month', current)::integer;

IF month = 1 THEN fiscal_year := mod(year,100); year := year-1; month := 11; quarter := 4;
ELSEIF month >= 2 AND month <= 4 THEN fiscal_year := mod(year+1,100); month := 2; quarter := 1;
ELSEIF month >= 5 AND month <= 7 THEN fiscal_year := mod(year+1,100); month := 5; quarter := 2;
ELSEIF month >= 8 AND month <= 10 THEN fiscal_year := mod(year+1,100); month := 8; quarter := 3;
ELSE fiscal_year := mod(year+1,100); month := 11; quarter := 4;
END IF;


current := date(year||'-'||month||'-01');
WHILE num > 0 AND fiscal_year >= 0 LOOP
RETURN QUERY SELECT fiscal_year,quarter,current::timestamp with time zone,(current+INTERVAL '3 month'-INTERVAL '1 second')::timestamp with time zone;


current := current-INTERVAL '3 month';
quarter := quarter-1;
IF quarter = 0 THEN
fiscal_year := fiscal_year-1;
quarter := 4;
END IF;


num := num-1;
END LOOP;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION nvsdm.fn_fiscal_year(integer)
  OWNER TO sys_sdm;