减少应用和数据库之间的网络传输。所有的 SQL 语句都存储在数据库服务器中，应用程序只需要发送函数调用并获取除了结果，避免了发送多个 SQL 语句并等待结果。
提高应用的性能。因为自定义函数和存储过程进行了预编译并存储在数据库服务器中。
可重用性。存储过程和函数的功能可以被多个应用同时使用。
导致软件开发缓慢。因为存储过程需要单独学习，而且很多开发人员并不具备这种技能。
不易进行版本管理和代码调试。
不同数据库管理系统之间无法移植，语法存在较大的差异。
PL/pgSQL 简单易学，无论是否具有编程基础都能够很快学会。
PL/pgSQL 是 PostgreSQL 默认支持的过程语言，PL/pgSQL 开发的自定义函数可以和内置函数一样使用。
PL/pgSQL 提高了许多强大的功能，例如游标，可以实现复杂的函数。
PL/pgSQL 代码块结构
[ <<label>> ]
[ DECLARE
declarations ]
BEGIN
statements;
...
END [ label ];
DO $$
DECLARE
name text;
BEGIN
name := 'PL/pgSQL';
RAISE NOTICE 'Hello %!', name;
END $$;
DO
'DECLARE
name text;
BEGIN
name := ''PL/pgSQL'';
RAISE NOTICE ''Hello %!'', name;
END ';
postgres=# DO $$
postgres$# DECLARE
postgres$# name text;
postgres$# BEGIN
postgres$# name := 'PL/pgSQL';
postgres$# RAISE NOTICE 'Hello %!', name;
postgres$# END $$;
NOTICE: Hello PL/pgSQL!
嵌套子块
DO $$
<<outer_block>>
DECLARE
name text;
BEGIN
name := 'outer_block';
RAISE NOTICE 'This is %', name;
DECLARE
name text := 'sub_block';
BEGIN
RAISE NOTICE 'This is %', name;
RAISE NOTICE 'The name from the outer block is %', outer_block.name;
END;
RAISE NOTICE 'This is %', name;
END outer_block $$;
NOTICE: This is outer_block
NOTICE: This is sub_block
NOTICE: The name from the outer block is outer_block
NOTICE: This is outer_block
声明与赋值
变量
variable_name data_type [ NOT NULL ] [ { DEFAULT | := | = } expression ];
user_id integer;
quantity numeric(5) DEFAULT 0;
url varchar := 'http://mysite.com';
myrow tablename%ROWTYPE;
myfield tablename.columnname%TYPE;
amount quantity%TYPE;
arow RECORD;
newname ALIAS FOR oldname;
常量
DO $$
DECLARE
PI CONSTANT NUMERIC := 3.14159265;
radius NUMERIC;
BEGIN
radius := 1.0;
RAISE NOTICE 'The area is %', PI * radius * radius;
END $$;
NOTICE: The area is 3.1415926500
控制结构
IF 语句
IF … THEN … END IF
IF … THEN … ELSE … END IF
IF … THEN … ELSIF … THEN … ELSE … END IF
IF boolean-expression THEN
statements
END IF;
DO $$
BEGIN
IF 2 > 3 THEN
RAISE NOTICE '2 大于 3';
END IF;
IF 2 < 3 THEN
RAISE NOTICE '2 小于 3';
END IF;
END $$;
NOTICE: 2 小于 3
IF boolean-expression THEN
statements
ELSE
other-statements
END IF;
DO $$
BEGIN
IF 2 > 3 THEN
RAISE NOTICE '2 大于 3';
ELSE
RAISE NOTICE '2 小于 3';
END IF;
END $$;
NOTICE: 2 小于 3
IF boolean-expression THEN
statements
[ ELSIF boolean-expression THEN
statements ]
[ ELSIF boolean-expression THEN
statements ]
...
[ ELSE
statements ]
END IF;
DO $$
DECLARE
i integer := 3;
j integer := 3;
BEGIN
IF i > j THEN
RAISE NOTICE 'i 大于 j';
ELSIF i < j THEN
RAISE NOTICE 'i 小于 j';
ELSE
RAISE NOTICE 'i 等于 j';
END IF;
END $$;
NOTICE: i 等于 j
DO
CASE 语句
CASE search-expression
WHEN expression [, expression [ ... ]] THEN
statements
[ WHEN expression [, expression [ ... ]] THEN
statements
... ]
[ ELSE
statements ]
END CASE;
DO $$
DECLARE
i integer := 3;
BEGIN
CASE i
WHEN 1, 2 THEN
RAISE NOTICE 'one or two';
WHEN 3, 4 THEN
RAISE NOTICE 'three or four';
ELSE
RAISE NOTICE 'other value';
END CASE;
END $$;
NOTICE: three or four
CASE
WHEN boolean-expression THEN
statements
[ WHEN boolean-expression THEN
statements
... ]
[ ELSE
statements ]
END CASE;
DO $$
DECLARE
i integer := 3;
BEGIN
CASE
WHEN i BETWEEN 0 AND 10 THEN
RAISE NOTICE 'value is between zero and ten';
WHEN i BETWEEN 11 AND 20 THEN
RAISE NOTICE 'value is between eleven and twenty';
ELSE
RAISE NOTICE 'other value';
END CASE;
END $$;
循环语句
[ <<label>> ]
LOOP
statements
END LOOP [ label ];
DO $$
DECLARE
i integer := 0;
BEGIN
LOOP
EXIT WHEN i = 5;
i := i + 1;
RAISE NOTICE 'Loop: %', i;
END LOOP;
END $$;
NOTICE: Loop: 1
NOTICE: Loop: 2
NOTICE: Loop: 3
NOTICE: Loop: 4
NOTICE: Loop: 5
EXIT [ label ] [ WHEN boolean-expression ];
CONTINUE [ label ] [ WHEN boolean-expression ];
DO $$
DECLARE
i integer := 0;
BEGIN
LOOP
EXIT WHEN i = 10;
i := i + 1;
CONTINUE WHEN mod(i, 2) = 1;
RAISE NOTICE 'Loop: %', i;
END LOOP;
END $$;
NOTICE: Loop: 2
NOTICE: Loop: 4
NOTICE: Loop: 6
NOTICE: Loop: 8
NOTICE: Loop: 10
[ <<label>> ]
WHILE boolean-expression LOOP
statements
END LOOP [ label ];
DO $$
DECLARE
i integer := 0;
BEGIN
WHILE i < 5 LOOP
i := i + 1;
RAISE NOTICE 'Loop: %', i;
END LOOP;
END $$;
NOTICE: Loop: 1
NOTICE: Loop: 2
NOTICE: Loop: 3
NOTICE: Loop: 4
NOTICE: Loop: 5
[ <<label>> ]
FOR name IN [ REVERSE ] expression .. expression [ BY expression ] LOOP
statements
END LOOP [ label ];
DO $$
BEGIN
FOR i IN 1..5 BY 2 LOOP
RAISE NOTICE 'Loop: %', i;
END LOOP;
END $$;
NOTICE: Loop: 1
NOTICE: Loop: 3
NOTICE: Loop: 5
[ <<label>> ]
FOR target IN query LOOP
statements
END LOOP [ label ];
DO $$
DECLARE
emp record;
BEGIN
FOR emp IN (SELECT * FROM employees LIMIT 5) LOOP
RAISE NOTICE 'Loop: %,%', emp.first_name, emp.last_name;
END LOOP;
END $$;
NOTICE: Loop: Steven,King
NOTICE: Loop: Neena,Kochhar
NOTICE: Loop: Lex,De Haan
NOTICE: Loop: Alexander,Hunold
NOTICE: Loop: Bruce,Ernst
[ <<label>> ]
FOREACH target [ SLICE number ] IN ARRAY expression LOOP
statements
END LOOP [ label ];
DO $$
DECLARE
x int;
BEGIN
FOREACH x IN ARRAY (ARRAY[[1,2,3],[4,5,6]])
LOOP
RAISE NOTICE 'x = %', x;
END LOOP;
END $$;
NOTICE: x = 1
NOTICE: x = 2
NOTICE: x = 3
NOTICE: x = 4
NOTICE: x = 5
NOTICE: x = 6
DO $$
DECLARE
x int[];
BEGIN
FOREACH x SLICE 1 IN ARRAY (ARRAY[[1,2,3],[4,5,6]])
LOOP
RAISE NOTICE 'row = %', x;
END LOOP;
END $$;
NOTICE: row = {1,2,3}
NOTICE: row = {4,5,6}
游标
声明游标变量；
打开游标；
从游标中获取结果；
判断是否存在更多结果。如果存在，执行第 3 步；否则，执行第 5 步；
关闭游标。
DO $$
DECLARE
rec_emp RECORD;
cur_emp CURSOR(p_deptid INTEGER) FOR
SELECT first_name, last_name, hire_date
FROM employees
WHERE department_id = p_deptid;
BEGIN
-- 打开游标
OPEN cur_emp(60);
LOOP
-- 获取游标中的记录
FETCH cur_emp INTO rec_emp;
-- 没有找到更多数据时退出循环
EXIT WHEN NOT FOUND;
RAISE NOTICE '%,% hired at:%' , rec_emp.first_name, rec_emp.last_name, rec_emp.hire_date;
END LOOP;
-- Close the cursor
CLOSE cur_emp;
END $$;
NOTICE: Alexander,Hunold hired at:2006-01-03
NOTICE: Bruce,Ernst hired at:2007-05-21
NOTICE: David,Austin hired at:2005-06-25
NOTICE: Valli,Pataballa hired at:2006-02-05
NOTICE: Diana,Lorentz hired at:2007-02-07
错误处理
报告错误和信息
RAISE level format;
DEBUG
LOG
NOTICE
INFO
WARNING
EXCEPTION
DO $$
BEGIN
RAISE DEBUG 'This is a debug text.';
RAISE INFO 'This is an information.';
RAISE LOG 'This is a log.';
RAISE WARNING 'This is a warning at %', now();
RAISE NOTICE 'This is a notice %%';
END $$;
INFO: This is an information.
WARNING: This is a warning at 2020-05-16 11:27:06.138569+08
NOTICE: This is a notice %
RAISE [ EXCEPTION ] format USING option = expression [, ... ];
RAISE [ EXCEPTION ] condition_name USING option = expression [, ... ];
RAISE [ EXCEPTION ] SQLSTATE 'sqlstate' USING option = expression [, ... ];
RAISE [ EXCEPTION ] USING option = expression [, ... ];
MESSAGE，设置错误消息。如果 RAISE 语句中已经包含了 format 字符串，不能再使用该选项。
DETAIL，指定错误详细信息。
HINT，设置一个提示信息。
ERRCODE，指定一个错误码（SQLSTATE）。可以是文档中的条件名称或者五个字符组成的 SQLSTATE 代码。
COLUMN、CONSTRAINT、DATATYPE、TABLE、SCHEMA，返回相关对象的名称。
RAISE EXCEPTION 'Nonexistent ID --> %', user_id
USING HINT = 'Please check your user ID';
RAISE 'Duplicate user ID: %', user_id USING ERRCODE = 'unique_violation';
RAISE 'Duplicate user ID: %', user_id USING ERRCODE = '23505';
RAISE division_by_zero;
RAISE SQLSTATE '22012';
检查断言
ASSERT condition [ , message ];
DO $$
DECLARE
i integer := 1;
BEGIN
ASSERT i = 0, 'i 的初始值应该为 0！';
END $$;
ERROR: i 的初始值应该为 0！
CONTEXT: PL/pgSQL function inline_code_block line 5 at ASSERT
捕获异常
[ <<label>> ]
[ DECLARE
declarations ]
BEGIN
statements
EXCEPTION
WHEN condition [ OR condition ... ] THEN
handler_statements
[ WHEN condition [ OR condition ... ] THEN
handler_statements
... ]
END;
DO $$
DECLARE
i integer := 1;
BEGIN
i := i / 0;
EXCEPTION
WHEN division_by_zero THEN
RAISE NOTICE '除零错误！';
WHEN OTHERS THEN
RAISE NOTICE '其他错误！';
END $$;
NOTICE: 除零错误！
自定义函数
CREATE [ OR REPLACE ] FUNCTION
name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
RETURNS rettype
AS $$
DECLARE
declarations
BEGIN
statements;
...
END; $$
LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION get_emp_count(p_deptid integer)
RETURNS integer
AS $$
DECLARE
ln_count integer;
BEGIN
select count(*) into ln_count
from employees
where department_id = p_deptid;
return ln_count;
END; $$
LANGUAGE plpgsql;
select department_id,department_name,get_emp_count(department_id)
from departments d;
department_id|department_name |get_emp_count|
-------------|--------------------|-------------|
10|Administration | 1|
20|Marketing | 2|
30|Purchasing | 6|
...
CREATE OR REPLACE FUNCTION get_emp_count(p_deptid integer, p_hiredate date)
RETURNS integer
AS $$
DECLARE
ln_count integer;
BEGIN
select count(*) into ln_count
from employees
where department_id = p_deptid and hire_date >= p_hiredate;
return ln_count;
END; $$
LANGUAGE plpgsql;
select department_id,department_name,get_emp_count(department_id),get_emp_count(department_id, '2005-01-01')
from departments d;
department_id|department_name |get_emp_count|get_emp_count|
-------------|--------------------|-------------|-------------|
10|Administration | 1| 0|
20|Marketing | 2| 1|
30|Purchasing | 6| 4|
...
CREATE OR REPLACE FUNCTION sum_num(
VARIADIC nums numeric[])
RETURNS numeric
AS $$
DECLARE ln_total numeric;
BEGIN
SELECT SUM(nums[i]) INTO ln_total
FROM generate_subscripts(nums, 1) t(i);
RETURN ln_total;
END; $$
LANGUAGE plpgsql;
SELECT sum_num(1,2), sum_num(1,2,3);
sum_num|sum_num|
-------|-------|
3| 6|
存储过程
CREATE [ OR REPLACE ] PROCEDURE
name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
AS $$
DECLARE
declarations
BEGIN
statements;
...
END; $$
LANGUAGE plpgsql;
CREATE OR REPLACE PROCEDURE update_emp(
p_empid in integer,
p_salary in numeric,
p_phone in varchar)
AS $$
BEGIN
update employees
set salary = p_salary,
phone_number = p_phone
where employee_id = p_empid;
END; $$
LANGUAGE plpgsql;
call update_emp(100, 25000, '515.123.4560');
事务管理
create table test(a int);
CREATE PROCEDURE transaction_test()
LANGUAGE plpgsql
AS $$
BEGIN
FOR i IN 0..9 LOOP
INSERT INTO test (a) VALUES (i);
IF i % 2 = 0 THEN
COMMIT;
ELSE
ROLLBACK;
END IF;
END LOOP;
END
$$;
CALL transaction_test();
select * from test;
a|
-|
0|
2|
4|
6|
8|