2026年7月18日(土) 6:26 Zsolt Parragi <[email protected]>:
>
> Hello!
Thanks for the review!
> Should the function preserve the disabled state of an event trigger?
>
> CREATE FUNCTION noop_evt() RETURNS event_trigger LANGUAGE plpgsql AS $$
> BEGIN
> END;
> $$;
> CREATE EVENT TRIGGER my_evt ON ddl_command_start EXECUTE FUNCTION
noop_evt();
> ALTER EVENT TRIGGER my_evt DISABLE;
> SELECT pg_get_event_trigger_ddl('my_evt', false);
>
> also REPLICA:
>
> ALTER EVENT TRIGGER my_evt ENABLE REPLICA;
> SELECT pg_get_event_trigger_ddl('my_evt', false);
>
> and OWNER:
>
> CREATE ROLE evt_owner_role SUPERUSER;
> ALTER EVENT TRIGGER my_evt OWNER TO evt_owner_role;
> SELECT pg_get_event_trigger_ddl('my_evt');
Correct.
> + appendStringInfoString(&filters, ", ");
> +
> + appendStringInfo(&filters, "'%s'", str);
>
> Shouldn't this use quote_literal_cstr?
Arguably it wouldn't strictly need to, as we'd only ever get plain ASCII
command tags here,
but it's not a critical performance choke point or anything, so might as
well use it to be
on the safe side.
Updated patch version attached.
Regards
Ian Barwick
From 4a1420d58fb1f6c6ed57e0862e7cdfb21d3e60fe Mon Sep 17 00:00:00 2001
From: Ian Barwick <[email protected]>
Date: Mon, 20 Jul 2026 20:01:21 +0900
Subject: [PATCH v2] 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.
---
doc/src/sgml/func/func-info.sgml | 28 +++
src/backend/utils/adt/ddlutils.c | 207 +++++++++++++++++++-
src/include/catalog/pg_proc.dat | 6 +
src/test/regress/expected/event_trigger.out | 67 +++++++
src/test/regress/sql/event_trigger.sql | 25 +++
5 files changed, 332 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index 69ef3857cfa..4c24214a55d 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -3902,6 +3902,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 the corresponding <literal>ENABLE</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..f88abc810da 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,204 @@ 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 = pstrdup(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 be 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 *arr = DatumGetArrayTypeP(evttagsDatum);
+ Datum *elems;
+ int nelems;
+ int i;
+ StringInfoData filters;
+
+ initStringInfo(&filters);
+
+ deconstruct_array_builtin(arr, 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));
+ }
+
+ append_ddl_option(&buf, pretty, 4, "WHEN TAG IN (%s)",
+ 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));
+
+ /* 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;
+ }
+
+ appendStringInfoChar(&buf, ';');
+ statements = lappend(statements, pstrdup(buf.data));
+ }
+
+ /* OWNER */
+ if (owner && OidIsValid(evtForm->evtowner))
+ {
+ char *owner = GetUserNameFromId(evtForm->evtowner, false);
+
+ resetStringInfo(&buf);
+ appendStringInfo(&buf, "ALTER EVENT TRIGGER %s OWNER TO %s;",
+ quote_identifier(evtname), quote_identifier(owner));
+ pfree(owner);
+ statements = lappend(statements, pstrdup(buf.data));
+ }
+
+ ReleaseSysCache(evtTup);
+
+ pfree(buf.data);
+ pfree(evtname);
+ pfree(evtevent);
+ pfree(proname);
+
+ 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; XXX
+ */
+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 1c55a4dea34..b179c01fdd6 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/test/regress/expected/event_trigger.out b/src/test/regress/expected/event_trigger.out
index 86ae50ce531..0bf58e3a425 100644
--- a/src/test/regress/expected/event_trigger.out
+++ b/src/test/regress/expected/event_trigger.out
@@ -84,6 +84,72 @@ 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 ENABLE;
+ ALTER EVENT TRIGGER regress_event_trigger OWNER TO regress_evt_superuser;
+(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 ENABLE;
+ ALTER EVENT TRIGGER regress_event_trigger2 OWNER TO regress_evt_superuser;
+(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 ENABLE;
+ ALTER EVENT TRIGGER regress_event_trigger OWNER TO regress_evt_superuser;
+(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)
+
+-- 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 +464,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..8cd38657cba 100644
--- a/src/test/regress/sql/event_trigger.sql
+++ b/src/test/regress/sql/event_trigger.sql
@@ -85,6 +85,30 @@ 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);
+
+-- 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 +320,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
