2026年7月23日(木) 19:32 Jim Jones <[email protected]>: > > On 22/07/2026 08:14, Zsolt Parragi wrote: > >> That did cross my mind, but while "enabled" feels more natural > >> grammatically, > >> "enable" corresponds to the "ENABLE" clause which is to be emitted (or > >> omitted). > >> Except when it's "DISABLE"... I can't think of any better phrasing > >> which is reasonably > >> short and conveys the meaning "emit the clause which indicates the trigger > >> is to > >> be disabled or enabled, and if the latter in what context". > > > > Yeah, I agree with this reasoning, I wasn't even sure to mention this > > or not. I only did because the ALTER EVENT TRIGGER documentation also > > mentions "disabled", so there's some precedent of using that form. > > > > Keeping it "enable" is fine with me. > > > > > > Since there seems to be a consensus among all reviewers I'll mark the CF > entry as ready for committer.
Thanks! Attaching an updated patch version which uses DatumGetArrayTypePCopy() instead of DatumGetArrayTypeP() (as pointed out in 1f108fc02). Regards Ian Barwick
From 315bacb1cc65fc4ee25a30865950209bb7f0e654 Mon Sep 17 00:00:00 2001 From: Ian Barwick <[email protected]> Date: Fri, 31 Jul 2026 09:51:34 +0900 Subject: [PATCH v5] Add pg_get_event_trigger_ddl() function Add a new SQL-callable function that returns the DDL statement needed to recreate an event trigger. It takes a name argument and an optional boolean argument for formatted output, and options to suppress output of ALTER EVENT TRIGGER statements to set the owner and whether/how the trigger is enabled. In passing, update the comment about the trigger firing states in trigger.h to mention they are used for event triggers as well. --- doc/src/sgml/func/func-info.sgml | 28 +++ src/backend/utils/adt/ddlutils.c | 217 +++++++++++++++++++- src/include/catalog/pg_proc.dat | 6 + src/include/commands/trigger.h | 3 +- src/test/regress/expected/event_trigger.out | 94 +++++++++ src/test/regress/sql/event_trigger.sql | 36 ++++ 6 files changed, 382 insertions(+), 2 deletions(-) diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml index 122fc740f1a..c056d8a6ad2 100644 --- a/doc/src/sgml/func/func-info.sgml +++ b/doc/src/sgml/func/func-info.sgml @@ -3907,6 +3907,34 @@ acl | {postgres=arwdDxtm/postgres,foo=r/postgres} <literal>TABLESPACE</literal> clause is omitted. </para></entry> </row> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>pg_get_event_trigger_ddl</primary> + </indexterm> + <function>pg_get_event_trigger_ddl</function> + ( <parameter>event_trigger</parameter> <type>name</type> + <optional>, <parameter>pretty</parameter> <type>boolean</type> + <literal>DEFAULT</literal> false</optional> + <optional>, <parameter>owner</parameter> <type>boolean</type> + <literal>DEFAULT</literal> true</optional> + <optional>, <parameter>enable</parameter> <type>boolean</type> + <literal>DEFAULT</literal> true</optional> ) + <returnvalue>setof text</returnvalue> + </para> + <para> + Reconstructs the <link linkend="sql-createeventtrigger"><command> + CREATE EVENT TRIGGER</command></link> statement for the specified event + trigger, followed by <link linkend="sql-altereventtrigger"><command>ALTER + EVENT TRIGGER</command></link> statements for owner, and whether the event + trigger is enabled or not. Each statement is returned as a separate row. + When <parameter>pretty</parameter> is true, the output + is pretty-printed. When <parameter>owner</parameter> is false, the + <literal>OWNER</literal> clause is omitted. When <parameter>enable</parameter> + is false, the corresponding <literal>ENABLE</literal> or + <literal>DISABLE</literal> clause is omitted. + </para></entry> + </row> <row> <entry role="func_table_entry"><para role="func_signature"> <indexterm> diff --git a/src/backend/utils/adt/ddlutils.c b/src/backend/utils/adt/ddlutils.c index a70f1c28655..5b2630674cc 100644 --- a/src/backend/utils/adt/ddlutils.c +++ b/src/backend/utils/adt/ddlutils.c @@ -26,9 +26,12 @@ #include "catalog/pg_collation.h" #include "catalog/pg_database.h" #include "catalog/pg_db_role_setting.h" +#include "catalog/pg_event_trigger.h" +#include "catalog/pg_proc.h" #include "catalog/pg_tablespace.h" #include "commands/tablespace.h" #include "common/relpath.h" +#include "commands/trigger.h" #include "funcapi.h" #include "mb/pg_wchar.h" #include "miscadmin.h" @@ -56,7 +59,8 @@ static List *pg_get_tablespace_ddl_internal(Oid tsid, bool pretty, bool no_owner static Datum pg_get_tablespace_ddl_srf(FunctionCallInfo fcinfo, Oid tsid); static List *pg_get_database_ddl_internal(Oid dbid, bool pretty, bool no_owner, bool no_tablespace); - +static List *pg_get_event_trigger_ddl_internal(Name evtTrigName, bool pretty, + bool owner, bool enable); /* * Helper to append a formatted string with optional pretty-printing. @@ -975,3 +979,214 @@ pg_get_database_ddl(PG_FUNCTION_ARGS) SRF_RETURN_DONE(funcctx); } } + +static List * +pg_get_event_trigger_ddl_internal(Name evtTrigName, bool pretty, + bool owner, bool enable) +{ + HeapTuple evtTup; + HeapTuple procTup; + Form_pg_event_trigger evtForm; + char *evtevent; + char *evtname; + Datum evttagsDatum; + bool evttagsIsNull; + Form_pg_proc proc; + char *proname; + StringInfoData buf; + List *statements = NIL; + + evtTup = SearchSysCache1(EVENTTRIGGERNAME, NameGetDatum(evtTrigName)); + + if (!HeapTupleIsValid(evtTup)) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("event trigger \"%s\" does not exist", NameStr(*evtTrigName)))); + + evtForm = (Form_pg_event_trigger) GETSTRUCT(evtTup); + evtname = pstrdup(NameStr(evtForm->evtname)); + evtevent = pstrdup(NameStr(evtForm->evtevent)); + + procTup = SearchSysCache1(PROCOID, ObjectIdGetDatum(evtForm->evtfoid)); + + if (!HeapTupleIsValid(procTup)) + elog(ERROR, + "cache lookup failed for function %u", evtForm->evtfoid); + + proc = (Form_pg_proc) GETSTRUCT(procTup); + proname = NameStr(proc->proname); + + initStringInfo(&buf); + + appendStringInfo(&buf, "CREATE EVENT TRIGGER %s", + quote_identifier(evtname)); + append_ddl_option(&buf, pretty, 4, "ON %s", + quote_identifier(evtevent)); + + /* + * Generate WHEN clause, if any filter events were specified. + * + * Note that as of PostgreSQL 20, the only filter_variable supported + * is "TAG", which is not stored in pg_event_trigger, so is hard-coded + * in the generated output. This also means that by definition, the AND + * clause supported by the CREATE EVENT TRIGGER grammar cannot actually + * be used in a valid statement, and thus will never be part of the + * generated output. + */ + evttagsDatum = SysCacheGetAttr(EVENTTRIGGEROID, evtTup, + Anum_pg_event_trigger_evttags, + &evttagsIsNull); + + if (!evttagsIsNull) + { + ArrayType *event_tags = DatumGetArrayTypePCopy(evttagsDatum); + Datum *elems; + int nelems; + int i; + StringInfoData filters; + + initStringInfo(&filters); + + deconstruct_array_builtin(event_tags, TEXTOID, &elems, NULL, &nelems); + + for (i = 0; i < nelems; ++i) + { + char *str = TextDatumGetCString(elems[i]); + + if (i) + appendStringInfoString(&filters, ", "); + + appendStringInfoString(&filters, quote_literal_cstr(str)); + + pfree(str); + } + + append_ddl_option(&buf, pretty, 4, "WHEN TAG IN (%s)", + filters.data); + + pfree(event_tags); + pfree(elems); + pfree(filters.data); + } + + /* + * Finally, add the EXECUTE clause. Note that although the command syntax + * accepts the keyword PROCEDURE for historical reasons, only an actual + * FUNCTION can be referenced, so we don't check the prokind. + */ + append_ddl_option(&buf, pretty, 4, "EXECUTE FUNCTION %s.%s();", + quote_identifier(get_namespace_name(proc->pronamespace)), + quote_identifier(proname)); + + ReleaseSysCache(procTup); + + statements = lappend(statements, pstrdup(buf.data)); + + /* OWNER */ + if (owner && OidIsValid(evtForm->evtowner)) + { + char *owner_name = GetUserNameFromId(evtForm->evtowner, false); + + resetStringInfo(&buf); + appendStringInfo(&buf, "ALTER EVENT TRIGGER %s OWNER TO %s;", + quote_identifier(evtname), quote_identifier(owner_name)); + pfree(owner_name); + statements = lappend(statements, pstrdup(buf.data)); + } + + /* ENABLE */ + if (enable) + { + resetStringInfo(&buf); + appendStringInfo(&buf, "ALTER EVENT TRIGGER %s ", + quote_identifier(evtname)); + + switch (evtForm->evtenabled) + { + case TRIGGER_DISABLED: + appendStringInfoString(&buf, "DISABLE"); + break; + case TRIGGER_FIRES_ON_ORIGIN: + appendStringInfoString(&buf, "ENABLE"); + break; + case TRIGGER_FIRES_ON_REPLICA: + appendStringInfoString(&buf, "ENABLE REPLICA"); + break; + case TRIGGER_FIRES_ALWAYS: + appendStringInfoString(&buf, "ENABLE ALWAYS"); + break; + default: + elog(ERROR, "unsupported evtenabled type %c", evtForm->evtenabled); + break; + } + + appendStringInfoChar(&buf, ';'); + statements = lappend(statements, pstrdup(buf.data)); + } + + ReleaseSysCache(evtTup); + + pfree(buf.data); + pfree(evtname); + pfree(evtevent); + + return statements; +} + +/* + * pg_get_event_trigger_ddl + * Get the DDL statement for an event trigger. + * + * Each row is a complete SQL statement. The first row is always the + * CREATE EVENT TRIGGER statement; subsequent elements are ALTER EVENT + * TRIGGER statements for ownership and enablement state, which can be + * optionally omitted. + */ +Datum +pg_get_event_trigger_ddl(PG_FUNCTION_ARGS) +{ + FuncCallContext *funcctx; + List *statements; + + if (SRF_IS_FIRSTCALL()) + { + MemoryContext oldcontext; + Name evtTrigName; + bool pretty; + bool owner; + bool enable; + + funcctx = SRF_FIRSTCALL_INIT(); + oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); + + evtTrigName = PG_GETARG_NAME(0); + pretty = PG_GETARG_BOOL(1); + owner = PG_GETARG_BOOL(2); + enable = PG_GETARG_BOOL(3); + + statements = pg_get_event_trigger_ddl_internal(evtTrigName, pretty, + owner, enable); + + funcctx->user_fctx = statements; + funcctx->max_calls = list_length(statements); + + MemoryContextSwitchTo(oldcontext); + } + + funcctx = SRF_PERCALL_SETUP(); + statements = (List *) funcctx->user_fctx; + + if (funcctx->call_cntr < funcctx->max_calls) + { + char *stmt; + + stmt = list_nth(statements, funcctx->call_cntr); + + SRF_RETURN_NEXT(funcctx, CStringGetTextDatum(stmt)); + } + else + { + list_free_deep(statements); + SRF_RETURN_DONE(funcctx); + } +} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index f8a021987b5..061f97ff7e4 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -8631,6 +8631,12 @@ proargtypes => 'regdatabase bool bool bool', proargnames => '{database,pretty,owner,tablespace}', proargdefaults => '{false,true,true}', prosrc => 'pg_get_database_ddl' }, +{ oid => '9067', descr => 'get DDL to recreate an event trigger', + proname => 'pg_get_event_trigger_ddl', prorows => '10', proretset => 't', + provolatile => 's', pronargdefaults => '3' , prorettype => 'text', + proargtypes => 'name bool bool bool', + proargnames => '{event_trigger,pretty,owner,enable}', + proargdefaults => '{false,true,true}', prosrc => 'pg_get_event_trigger_ddl' }, { oid => '2509', descr => 'deparse an encoded expression with pretty-print option', proname => 'pg_get_expr', provolatile => 's', prorettype => 'text', diff --git a/src/include/commands/trigger.h b/src/include/commands/trigger.h index 0c3d485abf4..337a76c0b9f 100644 --- a/src/include/commands/trigger.h +++ b/src/include/commands/trigger.h @@ -146,7 +146,8 @@ extern PGDLLIMPORT int SessionReplicationRole; /* * States at which a trigger can be fired. These are the - * possible values for pg_trigger.tgenabled. + * possible values for pg_trigger.tgenabled and + * pg_event_trigger.evtenabled. */ #define TRIGGER_FIRES_ON_ORIGIN 'O' #define TRIGGER_FIRES_ALWAYS 'A' diff --git a/src/test/regress/expected/event_trigger.out b/src/test/regress/expected/event_trigger.out index 86ae50ce531..6f1506757c1 100644 --- a/src/test/regress/expected/event_trigger.out +++ b/src/test/regress/expected/event_trigger.out @@ -84,6 +84,99 @@ create event trigger regress_event_trigger2 on ddl_command_start execute procedure test_event_trigger(); -- OK comment on event trigger regress_event_trigger is 'test comment'; +-- further output may display the trigger owner name, so ensure it +-- is set to something predictable. +CREATE ROLE regress_evt_superuser SUPERUSER; +ALTER EVENT TRIGGER regress_event_trigger OWNER TO regress_evt_superuser; +ALTER EVENT TRIGGER regress_event_trigger2 OWNER TO regress_evt_superuser; +-- these are all OK - checks the DDL output without a tag filter and with one +SELECT pg_get_event_trigger_ddl('regress_event_trigger'); + pg_get_event_trigger_ddl +--------------------------------------------------------------------------------------------------------------- + CREATE EVENT TRIGGER regress_event_trigger ON ddl_command_start EXECUTE FUNCTION public.test_event_trigger(); + ALTER EVENT TRIGGER regress_event_trigger OWNER TO regress_evt_superuser; + ALTER EVENT TRIGGER regress_event_trigger ENABLE; +(3 rows) + +SELECT pg_get_event_trigger_ddl('regress_event_trigger2'); + pg_get_event_trigger_ddl +---------------------------------------------------------------------------------------------------------------------------------------------------------------- + CREATE EVENT TRIGGER regress_event_trigger2 ON ddl_command_start WHEN TAG IN ('CREATE TABLE', 'CREATE FUNCTION') EXECUTE FUNCTION public.test_event_trigger(); + ALTER EVENT TRIGGER regress_event_trigger2 OWNER TO regress_evt_superuser; + ALTER EVENT TRIGGER regress_event_trigger2 ENABLE; +(3 rows) + +-- check formatted output +SELECT pg_get_event_trigger_ddl('regress_event_trigger', true); + pg_get_event_trigger_ddl +--------------------------------------------------------------------------- + CREATE EVENT TRIGGER regress_event_trigger + + ON ddl_command_start + + EXECUTE FUNCTION public.test_event_trigger(); + ALTER EVENT TRIGGER regress_event_trigger OWNER TO regress_evt_superuser; + ALTER EVENT TRIGGER regress_event_trigger ENABLE; +(3 rows) + +-- check output without owner/enable type +SELECT pg_get_event_trigger_ddl('regress_event_trigger', owner:=false); + pg_get_event_trigger_ddl +--------------------------------------------------------------------------------------------------------------- + CREATE EVENT TRIGGER regress_event_trigger ON ddl_command_start EXECUTE FUNCTION public.test_event_trigger(); + ALTER EVENT TRIGGER regress_event_trigger ENABLE; +(2 rows) + +SELECT pg_get_event_trigger_ddl('regress_event_trigger', enable:=false); + pg_get_event_trigger_ddl +--------------------------------------------------------------------------------------------------------------- + CREATE EVENT TRIGGER regress_event_trigger ON ddl_command_start EXECUTE FUNCTION public.test_event_trigger(); + ALTER EVENT TRIGGER regress_event_trigger OWNER TO regress_evt_superuser; +(2 rows) + +SELECT pg_get_event_trigger_ddl('regress_event_trigger', owner:=false, enable:=false); + pg_get_event_trigger_ddl +--------------------------------------------------------------------------------------------------------------- + CREATE EVENT TRIGGER regress_event_trigger ON ddl_command_start EXECUTE FUNCTION public.test_event_trigger(); +(1 row) + +-- check output with different enable values +ALTER EVENT TRIGGER regress_event_trigger DISABLE; +SELECT pg_get_event_trigger_ddl('regress_event_trigger', owner:=false); + pg_get_event_trigger_ddl +--------------------------------------------------------------------------------------------------------------- + CREATE EVENT TRIGGER regress_event_trigger ON ddl_command_start EXECUTE FUNCTION public.test_event_trigger(); + ALTER EVENT TRIGGER regress_event_trigger DISABLE; +(2 rows) + +ALTER EVENT TRIGGER regress_event_trigger ENABLE ALWAYS; +SELECT pg_get_event_trigger_ddl('regress_event_trigger', owner:=false); + pg_get_event_trigger_ddl +--------------------------------------------------------------------------------------------------------------- + CREATE EVENT TRIGGER regress_event_trigger ON ddl_command_start EXECUTE FUNCTION public.test_event_trigger(); + ALTER EVENT TRIGGER regress_event_trigger ENABLE ALWAYS; +(2 rows) + +ALTER EVENT TRIGGER regress_event_trigger ENABLE REPLICA; +SELECT pg_get_event_trigger_ddl('regress_event_trigger', owner:=false); + pg_get_event_trigger_ddl +--------------------------------------------------------------------------------------------------------------- + CREATE EVENT TRIGGER regress_event_trigger ON ddl_command_start EXECUTE FUNCTION public.test_event_trigger(); + ALTER EVENT TRIGGER regress_event_trigger ENABLE REPLICA; +(2 rows) + +-- reset enable state +ALTER EVENT TRIGGER regress_event_trigger ENABLE; +-- will return an empty result set +SELECT pg_get_event_trigger_ddl(NULL); + pg_get_event_trigger_ddl +-------------------------- +(0 rows) + +-- should fail, no argument +SELECT pg_get_event_trigger_ddl(); +ERROR: function pg_get_event_trigger_ddl() does not exist +LINE 1: SELECT pg_get_event_trigger_ddl(); + ^ +DETAIL: No function of that name accepts the given number of arguments. -- drop as non-superuser should fail create role regress_evt_user; set role regress_evt_user; @@ -398,6 +491,7 @@ SELECT * FROM dropped_objects WHERE object_type = 'schema'; (3 rows) DROP ROLE regress_evt_user; +DROP ROLE regress_evt_superuser; DROP EVENT TRIGGER regress_event_trigger_drop_objects; DROP EVENT TRIGGER undroppable; -- Event triggers on relations. diff --git a/src/test/regress/sql/event_trigger.sql b/src/test/regress/sql/event_trigger.sql index d0e6ba295fe..43db4b9615d 100644 --- a/src/test/regress/sql/event_trigger.sql +++ b/src/test/regress/sql/event_trigger.sql @@ -85,6 +85,41 @@ create event trigger regress_event_trigger2 on ddl_command_start -- OK comment on event trigger regress_event_trigger is 'test comment'; +-- further output may display the trigger owner name, so ensure it +-- is set to something predictable. +CREATE ROLE regress_evt_superuser SUPERUSER; +ALTER EVENT TRIGGER regress_event_trigger OWNER TO regress_evt_superuser; +ALTER EVENT TRIGGER regress_event_trigger2 OWNER TO regress_evt_superuser; + +-- these are all OK - checks the DDL output without a tag filter and with one +SELECT pg_get_event_trigger_ddl('regress_event_trigger'); +SELECT pg_get_event_trigger_ddl('regress_event_trigger2'); + +-- check formatted output +SELECT pg_get_event_trigger_ddl('regress_event_trigger', true); + +-- check output without owner/enable type +SELECT pg_get_event_trigger_ddl('regress_event_trigger', owner:=false); +SELECT pg_get_event_trigger_ddl('regress_event_trigger', enable:=false); +SELECT pg_get_event_trigger_ddl('regress_event_trigger', owner:=false, enable:=false); + +-- check output with different enable values +ALTER EVENT TRIGGER regress_event_trigger DISABLE; +SELECT pg_get_event_trigger_ddl('regress_event_trigger', owner:=false); +ALTER EVENT TRIGGER regress_event_trigger ENABLE ALWAYS; +SELECT pg_get_event_trigger_ddl('regress_event_trigger', owner:=false); +ALTER EVENT TRIGGER regress_event_trigger ENABLE REPLICA; +SELECT pg_get_event_trigger_ddl('regress_event_trigger', owner:=false); + +-- reset enable state +ALTER EVENT TRIGGER regress_event_trigger ENABLE; + +-- will return an empty result set +SELECT pg_get_event_trigger_ddl(NULL); + +-- should fail, no argument +SELECT pg_get_event_trigger_ddl(); + -- drop as non-superuser should fail create role regress_evt_user; set role regress_evt_user; @@ -296,6 +331,7 @@ DROP OWNED BY regress_evt_user; SELECT * FROM dropped_objects WHERE object_type = 'schema'; DROP ROLE regress_evt_user; +DROP ROLE regress_evt_superuser; DROP EVENT TRIGGER regress_event_trigger_drop_objects; DROP EVENT TRIGGER undroppable; -- 2.52.0
