jbarrameda jbarrameda - 1 month ago 12
SQL Question

Create or replace trigger postgres

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,


  1. Is such command planned for Postgres at all?


Answer

Postres has transaction DDL so BEGIN > DROP > CREATE > COMMIT is the equivalent ofCREATE OR REPLACE`

https://wiki.postgresql.org/wiki/Transactional_DDL_in_PostgreSQL:_A_Competitive_Analysis is a nice writeup of how postgre's transactional DDL compares to other systems (such as oracle)

Current postgres planned features regarding triggers (https://wiki.postgresql.org/wiki/Todo#Triggers) do not include adding the REPLACE syntax