PostgreSQL之扩展SQL：触发器、自定义函数、存储过程

触发器

　　CREATE TRIGGER创建一个新触发器。该触发器将被关联到指定的表、视图或者外部表并且在表上发生特定操作时将执行指定的函数function_name。

CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }
    ON table_name
    [ FROM referenced_table_name ]
    [ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ]
    [ REFERENCING { { OLD | NEW } TABLE [ AS ] transition_relation_name } [ ... ] ]
    [ FOR [ EACH ] { ROW | STATEMENT } ]
    [ WHEN ( condition ) ]
    EXECUTE { FUNCTION | PROCEDURE } function_name ( arguments )

这里的event可以是下列之一：

    INSERT
    UPDATE [ OF column_name [, ... ] ]
    DELETE
    TRUNCATE

FOR [ EACH ] { ROW | STATEMENT } :这指定该触发器函数是应该为该触发器事件影响的每一行被引发一次，还是只为每个 SQL 语句被引发一次。默认值是FOR EACH STATEMENT,约束触发器只能被指定为FOR EACH ROW。

何时	事件	行级	语句级
BEFORE	INSERT/UPDATE/DELETE	表和外部表	表、视图和外部表
	TRUNCATE	—	表
AFTER	INSERT/UPDATE/DELETE	表和外部表	表、视图和外部表
	TRUNCATE	—	表
INSTEAD OF	INSERT/UPDATE/DELETE	视图	—
	TRUNCATE	—	—

-- 只有列balance具有真正被改变的值时才执行该函数
CREATE TRIGGER check_update
    BEFORE UPDATE ON accounts
    FOR EACH ROW
    WHEN (OLD.balance IS DISTINCT FROM NEW.balance)
    EXECUTE FUNCTION check_account_update();

自定义函数

CREATE [ OR REPLACE ] FUNCTION
    name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
    [ RETURNS rettype
      | RETURNS TABLE ( column_name column_type [, ...] ) ]
  { LANGUAGE lang_name
    | TRANSFORM { FOR TYPE type_name } [, ... ]
    | WINDOW
    | IMMUTABLE | STABLE | VOLATILE | [ NOT ] LEAKPROOF
    | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
    | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
    | PARALLEL { UNSAFE | RESTRICTED | SAFE }
    | COST execution_cost
    | ROWS result_rows
    | SUPPORT support_function
    | SET configuration_parameter { TO value | = value | FROM CURRENT }
    | AS 'definition'
    | AS 'obj_file', 'link_symbol'
  } ...

• argmode：参数的模式：IN、OUT、INOUT或者VARIADIC。如果省略，默认为IN。只有OUT参数能跟在一个VARIADIC参数后面。还有，OUT和INOUT参数不能和RETURNS TABLE符号一起使用。
• argtype：该函数参数（如果有）的数据类型（可以是模式限定的）。参数类型可以是基本类型、组合类型或者域类型，或者可以引用一个表列的类型。可以写table_name.column_name%TYPE来引用一列的类型。
• rettype：返回数据类型（可能被模式限定）。返回类型可以是一种基本类型、组合类型或者域类型，也可以引用一个表列的类型。当有OUT或者INOUT参数时，可以省略RETURNS子句。如果有多个输出参数，则为RECORD，否则与单个输出参数的类型相同。

CREATE FUNCTION dup(in int, out f1 int, out f2 text)
    AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
    LANGUAGE SQL;

SELECT * FROM dup(42);
----------
42 | 42 is text

 

存储过程

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'
  } ...

　　函数创建也适用于过程，使用CALL来执行过程：

CREATE OR REPLACE PROCEDURE tt(in p_ids int8[] ) AS $BODY$
DECLARE
   p_user_id int8; 
BEGIN  
  RAISE notice  '----------------BEGIN----------------';
  --------------------------------跨库处理数据 开始--------------------------------
  -- 1.先执行dblink_connect保持连接
  PERFORM dblink_connect('account_merge_coon','hostaddr=192.168.100.40 port=5432 dbname=capture_nacos_bak user=postgres password=postgres');
  -- 2. 执行BEGIN命令
  PERFORM dblink_exec('account_merge_coon', 'BEGIN');
  
  -- 3. 执行数据操作（update，insert，create等命令）
  -- 4. 执行事务提交
  PERFORM dblink_exec('account_merge_coon', 'COMMIT');
  -- 5. 解除连接
  PERFORM dblink_disconnect('account_merge_coon');
  
  --------------------------------跨库处理数据 结束--------------------------------
  RAISE notice  '----------------END----------------';
  -- 异常回滚
  EXCEPTION
      WHEN SQLSTATE '23505' THEN ROLLBACK;RAISE notice '违反唯一约束（UNIQUE VIOLATION）';
      WHEN SQLSTATE '22003' THEN ROLLBACK;RAISE notice '数字值超出范围（NUMERIC VALUE OUT OF RANGE）';
      WHEN OTHERS THEN ROLLBACK;PERFORM dblink_disconnect('account_merge_coon');
   
END;
$BODY$ LANGUAGE plpgsql;


call tt(array[101000000248]::int8[]);

　　mybatis调用存储过程方式如下：

<select id="accountMerging" parameterType="java.util.Map" statementType="CALLABLE" >
           <![CDATA[
            CALL tt(#{p_ids,mode=IN,typeHandler=com.***.typeHandler.ArrayTypeHandler}) ]]>
</select>

　　注意事项：

• mybatis传数组时，需要带上typeHandler
• EXCEPTION语句块里面的WHEN条件，是PostgreSQL的错误码
• 没有EXCEPTION语句块时，有异常自动回滚
• dblink异常时，需要手动关闭连接