Create or replace trigger postgres
SqlPostgresqlDdlDatabase TriggerSql Problem Overview
I want to "create or replace" a trigger for a postgres table. However, there is not such sql expression.
I see that I can do a "DROP TRIGGER IF EXISTS
" first (http://www.postgresql.org/docs/9.5/static/sql-droptrigger.html).
My question are:
- Is there a recommended/better option than (
DROP
+CREATE
trigger) - Is there a reason why there is not such "create or replace trigger" (which might imply that I should not be wanting to do it)
Note that there is a "Create or Replace Trigger
" in oracle (https://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_triggers.htm). Then,
- Is such command planned for Postgres at all?
Sql Solutions
Solution 1 - Sql
No way to create or replace a trigger but can do this way
DROP TRIGGER IF EXISTS yourtrigger_name on "yourschemaname"."yourtablename";
Solution 2 - Sql
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.
Solution 3 - Sql
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();
Solution 4 - Sql
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 $$;
Solution 5 - Sql
As of PostgreSQL 14, CREATE TRIGGER
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.
Solution 6 - Sql
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;
$$
Solution 7 - Sql
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)