Create or replace trigger postgres

Question

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:

1. Is there a recommended/better option than (DROP + CREATE trigger)
2. 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,

3. Is such command planned for Postgres at all?

Answer 1

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

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

Answer 2

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.

Answer 3

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();

Answer 4

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

Answer 5

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

Answer 6

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)