Postgresql函数总结_sql

概述在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 i

在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，则除了定义的时候要注意 declarers 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,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,(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;