PostgreSQL官方文档
http://www.postgres.cn/docs/10/index.html
Postgresql过程语言官方文档
http://www.postgres.cn/docs/10/xplang.html
Postgresql查看当前版本
select version();
PostgreSQL函数创建格式
drop function if exists [函数名];
create function [函数名] ([参数]) returns [返回值] as
$$
declare
[变量声明]
begin
[sql逻辑]
end;
$$
language 'plpgsql';
示例1
create or replace function calculate (num1 int, num2 int, opr char(1)) returns int as
$$
declare
num int;
begin
--raise notice 'num1:%,num2:%;opr:%', num1, num2, opr;
if opr = '-' then num := num1 - num2;
elseif opr = '+' then num := num1 + num2;
elseif opr = '*' then num := num1 * num2;
elseif opr = '/' then num := num1 / num2;
else
raise exception 'opr参数值只能为(-,+,*,/):当前opr的值:%', opr;
end if;
return num;
end
$$
language 'plpgsql';
示例2
create or replace function calculate (num1 int, num2 int, opr char(1), out num int) as
$$
begin
if opr = '-' then num := num1 - num2;
elseif opr = '+' then num := num1 + num2;
elseif opr = '*' then num := num1 * num2;
elseif opr = '/' then num := num1 / num2;
else
raise exception 'opr参数值只能为(-,+,*,/):当前opr的值:%', opr;
end if;
end
$$
language 'plpgsql';
调用
select calculate(1, 1, '+');
基本语法
以下简要摘自官方文档,只做抛砖引玉的作用,更详细的说明请查看上面提供官方文档地址。
1、条件
IF ... THEN ... END IF
IF ... THEN ... ELSE ... END IF
IF ... THEN ... ELSIF ... THEN ... ELSE ... END IF
CASE ... WHEN ... THEN ... ELSE ... END CASE
CASE WHEN ... THEN ... ELSE ... END CASE
2、循环
LOOP
[<<label>>]
LOOP
...
IF ... THEN
CONTINUE [label] [WHEN boolean-expression];
END IF;
EXIT [label] [WHEN boolean-expression];
END LOOP [label];
WHILE
[<<label>>]
WHILE boolean-expression LOOP
...
END LOOP [label];
FOR
[<<label>>]
FOR name IN [REVERSE] expression .. expression [BY expression] LOOP
...
END LOOP [label];
通过查询结果循环
[<<label>>]
FOR target IN query LOOP
...
END LOOP [label];
通过数组循环
[<<label>>]
FOREACH target [SLICE number] IN ARRAY expression LOOP
...
END LOOP [label];
捕获错误抛异常
[<<label>>]
DECLARE
...
BEGIN
...
EXCEPTION
WHEN condition [ OR condition ... ] THEN
RAISE NOTICE '提示';
WHEN condition [ OR condition ... ] THEN
RAISE NOTICE '提示,%,%', bianliang1,bianliang2;
END;