Proposal for CREATE OR REPLACE EVENT TRIGGER in PostgreSQL - Mailing list pgsql-hackers

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