创建或替换触发器 postgres

我想“创建或替换”postgres 表的触发器。但是，没有这样的 sql 表达式。

我看到我可以先做一个“ DROP TRIGGER IF EXISTS”(http://www.postgresql.org/docs/9.5/static/sql-droptrigger.html)。

我的问题是:

  1. 是否有比(DROP + CREATE触发器)更好的推荐选项
  2. 为什么没有这样的“创建或替换触发器”(这可能意味着我不应该想要这样做)

注意，在 Oracle (https://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_triggers.htm)中有一个“ Create or Replace Trigger”,

  1. 这样的指挥计划是为 Postgres 准备的吗？
66895 次浏览
小开
最佳答案

Postgresql has transaction DDL so BEGIN > DROP > CREATE > COMMIT is the equivalent of CREATE OR REPLACE

This is a nice write-up of how postgre's transactional DDL compares to other systems (such as oracle)

Current postgres planned features regarding triggers do not include adding the REPLACE syntax.

小开

No way to create or replace a trigger but can do this way

DROP TRIGGER IF EXISTS yourtrigger_name on "yourschemaname"."yourtablename";
小开

You should use two statements: one for drop trigger and another for creating a trigger.

Example:

DROP TRIGGER IF EXISTS my_trigger
ON my_schema.my_table;
CREATE TRIGGER my_trigger
BEFORE INSERT OR UPDATE
ON my_schema.my_table
FOR EACH ROW EXECUTE PROCEDURE my_schema.my_function();
小开

you can use below code.

DO $$ BEGIN


CREATE (trigger, type , ...);


EXCEPTION
WHEN others THEN null;
END $$;

sample:

DO $$ BEGIN


CREATE TRIGGER trigger_workIDExist
BEFORE INSERT OR UPDATE ON "GalleryModel"
FOR EACH ROW EXECUTE PROCEDURE check_workIDExist();


EXCEPTION
WHEN others THEN null;
END $$;
小开

You can combine CREATE OR REPLACE FUNCTION trigger_function with the following script in your SQL:

DO $$
BEGIN
IF NOT EXISTS(SELECT *
FROM information_schema.triggers
WHERE event_object_table = 'table_name'
AND trigger_name = 'trigger_name'
)
THEN
CREATE TRIGGER trigger_name AFTER INSERT ON table_name FOR EACH ROW EXECUTE PROCEDURE trigger_function();
END IF;
END;
$$
小开

This is a Python script which extracts all triggers from a postgresql dump file for a rebuild. I use many stacked views which works nicely with QGIS; this helped maintenance of the dependent views a lot.

Based on Ali Bagheri's great answer.

import pathlib
import re
import sys


re_pat_str = r'^\s*CREATE TRIGGER.*?;\s*$'


sql_wrapper_str = """
DO $$ BEGIN
{trigger_str}
EXCEPTION WHEN others THEN null;
END $$;
"""


if __name__ == "__main__":
sql_file = pathlib.Path(sys.argv[1])
with sql_file.open("r", encoding="utf8") as f:
sql_str = f.read()


re_pat = re.compile(re_pat_str, re.MULTILINE | re.DOTALL)


parts = []
for i, m in enumerate(re_pat.finditer(sql_str)):
parts.append(sql_wrapper_str.format(trigger_str=m[0].strip()))


new_sql_str = "\n".join(parts)
new_sql_file = sql_file.parent / f'{sql_file.stem}.trigger{sql_file.suffix}'
with new_sql_file.open("w", encoding="utf8") as f:
f.write(new_sql_str)
小开

As of PostgreSQL 14, ABC0 now also supports "OR REPLACE".

You can now use CREATE OR REPLACE TRIGGER ... (instead of using DROP TRIGGER IF EXISTS first).

This also seems to handle the case of partitioned tables sensibly:

Creating a row-level trigger on a partitioned table will cause an identical “clone” trigger to be created on each of its existing partitions; and any partitions created or attached later will have an identical trigger, too. If there is a conflictingly-named trigger on a child partition already, an error occurs unless CREATE OR REPLACE TRIGGER is used, in which case that trigger is replaced with a clone trigger. When a partition is detached from its parent, its clone triggers are removed.

Also noteworthy:

Currently, the OR REPLACE option is not supported for constraint triggers.


提交答案