Postgres 禁止/启用所有触发器_postgresql 禁用触发器

项目中需要对触发器进行批量禁止、启用操作，方法如下。

创建方法/函数/过程

/* Enable/disable all the triggers in database */
CREATE OR REPLACE FUNCTION fn_triggerall(DoEnable boolean) RETURNS integer AS 
$BODY$
DECLARE
mytables RECORD;
BEGIN
  FOR mytables IN SELECT relname FROM pg_class WHERE relhastriggers is true AND NOT relname LIKE 'pg_%'
  LOOP
    IF DoEnable THEN
      EXECUTE 'ALTER TABLE ' || mytables.relname || ' ENABLE TRIGGER ALL';
    ELSE
      EXECUTE 'ALTER TABLE ' || mytables.relname || ' DISABLE TRIGGER ALL';
    END IF;  
  END LOOP;

  RETURN 1;

END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

启用触发器

select fn_triggerall(true);
1

禁用触发器

select fn_triggerall(false);
1