PostgreSQL 11 preview - 增加CREATE PROCEDURE和CALL语法 (含过程内commit/rollback, 自治)
背景
PostgreSQL一直以来都是通过create function来创建函数和存储过程(return void),通过select或者perform(plpgsql内部调用函数)来调用函数。
通过inline code来模拟类似procedure的用法:
do language plpgsql $$
declare
-- ....
begin
-- ....
end;
$$;
PostgreSQL 11不知道出于什么原因,新增了两个语法create procedure和call调用存储过程。
实际上存储过程和函数最大的差异就是没有(或者说不需要)返回值.
Add SQL procedures, which can start and commit their own transactions (Peter Eisentraut)
They are created with the new CREATE PROCEDURE command and invoked via CALL. The new ALTER/DROP ROUTINE commands allows altering/dropping of procedures, functions, and aggregates.
语法
CREATE [ OR REPLACE ] PROCEDURE
name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
{ LANGUAGE lang_name
| TRANSFORM { FOR TYPE type_name } [, ... ]
| [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
| SET configuration_parameter { TO value | = value | FROM CURRENT }
| AS 'definition'
| AS 'obj_file', 'link_symbol'
} ...
存储过程内部的语法可以参考函数的写法。
例子
CREATE OR REPLACE PROCEDURE cs_create_job(v_job_id integer) AS $$
DECLARE
a_running_job_count integer;
BEGIN
LOCK TABLE cs_jobs IN EXCLUSIVE MODE;
SELECT count(*) INTO a_running_job_count FROM cs_jobs WHERE end_stamp IS NULL;
IF a_running_job_count > 0 THEN
COMMIT; -- free lock
RAISE EXCEPTION 'Unable to create a new job: a job is currently running'; -- (1)
END IF;
DELETE FROM cs_active_job;
INSERT INTO cs_active_job(job_id) VALUES (v_job_id);
BEGIN
INSERT INTO cs_jobs (job_id, start_stamp) VALUES (v_job_id, now());
EXCEPTION
WHEN unique_violation THEN -- (2)
-- don't worry if it already exists
END;
COMMIT;
END;
$$ LANGUAGE plpgsql;
(1)
The syntax of RAISE is considerably different from Oracle’s statement, although the basic case RAISE exception_name works similarly.
(2)
The exception names supported by PL/pgSQL are different from Oracle’s. The set of built-in exception names is much larger (see Appendix A). There is not currently a way to declare user-defined exception names, although you can throw user-chosen SQLSTATE values instead.
CREATE PROCEDURE insert_data(a integer, b integer)
LANGUAGE SQL
AS $$
INSERT INTO tbl VALUES (a);
INSERT INTO tbl VALUES (b);
$$;
CALL insert_data(1, 2);
procedure与函数不同的地方,没有返回值的部分,同时调用方法使用CALL而不是select procedure_name;