Postgresql函数总结

最新推荐文章于 2024-06-13 11:40:10 发布

windlyb

最新推荐文章于 2024-06-13 11:40:10 发布

阅读量1.3k

点赞数

CC 4.0 BY-SA版权

分类专栏：数据库文章标签： postgresql integer function date query table

本文链接：https://blog.youkuaiyun.com/windlyb/article/details/7910204

数据库专栏收录该内容

6 篇文章

订阅专栏

本文介绍了PostgreSQL中创建和使用函数的要点，包括函数的语法格式、返回值类型（TABLE或TEXT）的处理方式，以及如何在函数体内实现循环。提供了一个根据输入季度返回财政年和季度起始日期的示例函数。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

在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;