PostgreSQL 11 preview - 增加CREATE PROCEDURE和CALL语法 (含过程内commit/rollback, 自治)

标签

PostgreSQL , CREATE PROCEDURE , CALL , 增强 , 11

背景

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