Proposal for CREATE OR REPLACE EVENT TRIGGER in PostgreSQL

Dear pgsql-hackers,

One-line Summary:
Proposal to introduce the CREATE OR REPLACE syntax for EVENT TRIGGER in
PostgreSQL.

Business Use-case:
Currently, to modify an EVENT TRIGGER, one must drop and recreate it. This
proposal aims to introduce a CREATE OR REPLACE syntax for EVENT TRIGGER,
similar to other database objects in PostgreSQL, to simplify this process
and improve usability.

For example, suppose you would like to stop people from creating tables
without primary keys. You might run something like this.
CREATE OR REPLACE FUNCTION test_event_trigger_table_have_primary_key ()
RETURNS event_trigger LANGUAGE plpgsql AS $$
DECLARE
obj record;
object_types text[];
table_name text;
BEGIN
FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands () LOOP
RAISE NOTICE 'classid: % objid: %,object_type: % object_identity: %
schema_name: % command_tag: %' , obj.classid , obj.objid , obj.object_type
, obj.object_identity , obj.schema_name , obj.command_tag;
IF obj.object_type ~ 'table' THEN
table_name := obj.object_identity;
END IF;
object_types := object_types || obj.object_type;
END LOOP;
RAISE NOTICE 'table name: %' , table_name;
IF EXISTS ( SELECT FROM pg_index i JOIN pg_attribute a ON a.attrelid =
i.indrelid AND a.attnum = ANY (i.indkey) WHERE i.indisprimary AND
i.indrelid = table_name::regclass) IS FALSE THEN
RAISE EXCEPTION 'This table needs a primary key. Add a primary key
to create the table.';
END IF;
END;
$$;

CREATE EVENT TRIGGER trig_test_event_trigger_table_have_primary_key ON
ddl_command_end WHEN TAG IN ('CREATE TABLE') EXECUTE FUNCTION
test_event_trigger_table_have_primary_key ();
If you run this a second time, you will get an error. You can resolve this
with
DROP EVENT TRIGGER trig_test_event_trigger_table_have_primary_key;
CREATE EVENT TRIGGER trig_test_event_trigger_table_have_primary_key ON
ddl_command_end WHEN TAG IN ('CREATE TABLE') EXECUTE FUNCTION
test_event_trigger_table_have_primary_key ();
My suggestion is to have it so this would work.
CREATE OR REPLACE EVENT TRIGGER
trig_test_event_trigger_table_have_primary_key ON ddl_command_end WHEN TAG
IN ('CREATE TABLE') EXECUTE FUNCTION
test_event_trigger_table_have_primary_key ();
This would change the syntax from CREATE EVENT TRIGGER name
ON event
[ WHEN filter_variable IN (filter_value [, ... ]) [ AND ... ] ]
EXECUTE { FUNCTION | PROCEDURE } function_name() to CREATE [OR REPLACE]
EVENT TRIGGER name
ON event
[ WHEN filter_variable IN (filter_value [, ... ]) [ AND ... ] ]
EXECUTE { FUNCTION | PROCEDURE } function_name() at
https://www.postgresql.org/docs/current/sql-createeventtrigger.html.
User impact with the change:
This change will provide a more convenient and intuitive way for users to
modify EVENT TRIGGERS. It will eliminate the need to manually drop and
recreate the trigger when changes are needed.

Implementation details:
The implementation would involve modifying the parser to recognize the
CREATE OR REPLACE syntax for EVENT TRIGGER and appropriately handle the
recreation of the trigger.

Estimated Development Time:
Unknown at this time. Further analysis is required to provide an accurate
estimate.

Opportunity Window Period:
No specific end date. However, the sooner this feature is implemented, the
sooner users can benefit from the improved usability.

Budget Money:
Open to discussion.

Contact Information:
Peter Burbery
peter(dot)cullen(dot)burbery(at)gmail(dot)com

I look forward to your feedback on this proposal.

Best regards,
Peter Burbery