public inbox for [email protected]
help / color / mirror / Atom feedRe: CREATE TABLE LIKE INCLUDING TRIGGERS
12+ messages / 4 participants
[nested] [flat]
* Re: CREATE TABLE LIKE INCLUDING TRIGGERS
@ 2026-01-16 07:34 jian he <[email protected]>
2026-01-16 12:13 ` Re: CREATE TABLE LIKE INCLUDING TRIGGERS Andrey Borodin <[email protected]>
2026-01-21 12:13 ` Re: CREATE TABLE LIKE INCLUDING TRIGGERS Zsolt Parragi <[email protected]>
0 siblings, 2 replies; 12+ messages in thread
From: jian he @ 2026-01-16 07:34 UTC (permalink / raw)
To: Andrey Borodin <[email protected]>; +Cc: pgsql-hackers
On Fri, Jan 2, 2026 at 5:25 PM Andrey Borodin <[email protected]> wrote:
>
> + char *trigcomment; /* comment to apply to trigger, or NULL */
> No other Create*Stmt has a comment field. Comments seem to be handled through separate CommentStmt creation.
>
See CreateStatsStmt.stxcomment, IndexStmt.idxcomment.
We need CreateTrigStmt.trigcomment, because if INCLUDING COMMENTS is specified,
CreateTrigStmt.trigcomment can be used to hold the source object's comments.
> Some nitpicking about tests:
> 1. INSTEAD OF triggers on views - The error is tested, but should also test that statement-level VIEW triggers work
ok. test added.
> 2. Triggers on partitioned tables - What happens when you LIKE a partitioned table? Are partition triggers cloned?
no. only the trigger on the partitioned table itself will be cloned.
see tests ``create table parted_constr_copy (like parted_constr
including all);``
> 3. Cross-schema trigger functions - The function name reconstruction handles schemas, but is it tested?
>
ok. test added.
> + funcname = list_make2(makeString(schemaname),makeString(NameStr(procform->proname)));
> Other NameStr() are pstrdup()'d, maybe let's pstrdup() this too?
>
ok.
> + /* Reconstruct trigger old transition table */
> Second instance of this comment is wrong.
>
ok.
> + PG_KEYWORD("triggers", TRIGGERS, UNRESERVED_KEYWORD, BARE_LABEL)
> Won't this break some user SQLs?
it's marked as an un-reserved word, so it won't break any SQL, i think.
v4-0001, v4-0002 was removed, as Robert said in [1], now I am using
the same ugly
hack consistently, now code is more aligned with INCLUDING INDEXES, INCLUDING
STATISTICS.
pstrdup have been used in more places in generateClonedTriggerStmt.
--
jian
https://www.enterprisedb.com/
Attachments:
[text/x-patch] v5-0001-CREATE-TABLE-LIKE-INCLUDING-TRIGGERS.patch (41.8K, 2-v5-0001-CREATE-TABLE-LIKE-INCLUDING-TRIGGERS.patch)
download | inline diff:
From a83e1602a70673a6f8ab90dd0c08f1b206f0f404 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Fri, 16 Jan 2026 15:31:33 +0800
Subject: [PATCH v5 1/1] CREATE TABLE LIKE INCLUDING TRIGGERS
This will copy all source table's trigger to the new table. This functionality
also extends to CREATE FOREIGN TABLE LIKE statements.
Internal trigger (such as foreign key associated trigger) won't being copied to
new table. However this command will fail if the source table's trigger contain
whole-row reference.
If INCLUDING COMMENTS is specified, trigger comments will be copied to the new
table.
discussion: https://postgr.es/m/CACJufxHJAr2FjbeB6ghg_-N5dxX5JVnjKSLOUxOyt4TeaAWQkg@mail.gmail.com
commitfest: https://commitfest.postgresql.org/patch/6087
---
doc/src/sgml/ref/create_foreign_table.sgml | 11 +-
doc/src/sgml/ref/create_table.sgml | 13 +-
src/backend/catalog/index.c | 2 +
src/backend/commands/tablecmds.c | 8 +
src/backend/commands/trigger.c | 230 +++++++++++++++++-
src/backend/parser/gram.y | 9 +-
src/backend/parser/parse_utilcmd.c | 37 ++-
src/include/commands/trigger.h | 4 +
src/include/nodes/parsenodes.h | 4 +
src/include/parser/kwlist.h | 1 +
.../regress/expected/create_table_like.out | 10 +
src/test/regress/expected/triggers.out | 130 +++++++++-
src/test/regress/sql/create_table_like.sql | 7 +
src/test/regress/sql/triggers.sql | 67 ++++-
14 files changed, 519 insertions(+), 14 deletions(-)
diff --git a/doc/src/sgml/ref/create_foreign_table.sgml b/doc/src/sgml/ref/create_foreign_table.sgml
index 08a8ceeae75..68d0608b0a3 100644
--- a/doc/src/sgml/ref/create_foreign_table.sgml
+++ b/doc/src/sgml/ref/create_foreign_table.sgml
@@ -60,7 +60,7 @@ CREATE FOREIGN TABLE [ IF NOT EXISTS ] <replaceable class="parameter">table_name
<phrase>and <replaceable class="parameter">like_option</replaceable> is:</phrase>
-{ INCLUDING | EXCLUDING } { COMMENTS | CONSTRAINTS | DEFAULTS | GENERATED | STATISTICS | ALL }
+{ INCLUDING | EXCLUDING } { COMMENTS | CONSTRAINTS | DEFAULTS | GENERATED | STATISTICS | TRIGGERS | ALL }
<phrase>and <replaceable class="parameter">partition_bound_spec</replaceable> is:</phrase>
@@ -284,6 +284,15 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>INCLUDING TRIGGERS</literal></term>
+ <listitem>
+ <para>
+ All non-internal triggers are copied to the new table.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>INCLUDING ALL</literal></term>
<listitem>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 77c5a763d45..c6a0f6dae9b 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -88,7 +88,7 @@ class="parameter">referential_action</replaceable> ] [ ON UPDATE <replaceable cl
<phrase>and <replaceable class="parameter">like_option</replaceable> is:</phrase>
-{ INCLUDING | EXCLUDING } { COMMENTS | COMPRESSION | CONSTRAINTS | DEFAULTS | GENERATED | IDENTITY | INDEXES | STATISTICS | STORAGE | ALL }
+{ INCLUDING | EXCLUDING } { COMMENTS | COMPRESSION | CONSTRAINTS | DEFAULTS | GENERATED | IDENTITY | INDEXES | STATISTICS | STORAGE | TRIGGERS | ALL }
<phrase>and <replaceable class="parameter">partition_bound_spec</replaceable> is:</phrase>
@@ -672,7 +672,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<term><literal>INCLUDING COMMENTS</literal></term>
<listitem>
<para>
- Comments for the copied columns, constraints, and indexes will be
+ Comments for the copied columns, constraints, indexes and triggers will be
copied. The default behavior is to exclude comments, resulting in
the copied columns and constraints in the new table having no
comments.
@@ -776,6 +776,15 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</listitem>
</varlistentry>
+ <varlistentry id="sql-createtable-parms-like-opt-triggers">
+ <term><literal>INCLUDING TRIGGERS</literal></term>
+ <listitem>
+ <para>
+ All non-internal triggers on the original table will be created on the new table.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="sql-createtable-parms-like-opt-all">
<term><literal>INCLUDING ALL</literal></term>
<listitem>
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 43de42ce39e..b0b5a886fa1 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -2037,6 +2037,8 @@ index_constraint_create(Relation heapRelation,
trigger->deferrable = true;
trigger->initdeferred = initdeferred;
trigger->constrrel = NULL;
+ trigger->trigcomment = NULL;
+ trigger->transformed = true;
(void) CreateTrigger(trigger, NULL, RelationGetRelid(heapRelation),
InvalidOid, conOid, indexRelationId, InvalidOid,
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index f976c0e5c7e..6a25f6b7553 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -13864,6 +13864,8 @@ CreateFKCheckTrigger(Oid myRelOid, Oid refRelOid, Constraint *fkconstraint,
fk_trigger->deferrable = fkconstraint->deferrable;
fk_trigger->initdeferred = fkconstraint->initdeferred;
fk_trigger->constrrel = NULL;
+ fk_trigger->trigcomment = NULL;
+ fk_trigger->transformed = true;
trigAddress = CreateTrigger(fk_trigger, NULL, myRelOid, refRelOid,
constraintOid, indexOid, InvalidOid,
@@ -13909,6 +13911,8 @@ createForeignKeyActionTriggers(Oid myRelOid, Oid refRelOid, Constraint *fkconstr
fk_trigger->whenClause = NULL;
fk_trigger->transitionRels = NIL;
fk_trigger->constrrel = NULL;
+ fk_trigger->trigcomment = NULL;
+ fk_trigger->transformed = true;
switch (fkconstraint->fk_del_action)
{
@@ -13969,6 +13973,8 @@ createForeignKeyActionTriggers(Oid myRelOid, Oid refRelOid, Constraint *fkconstr
fk_trigger->whenClause = NULL;
fk_trigger->transitionRels = NIL;
fk_trigger->constrrel = NULL;
+ fk_trigger->trigcomment = NULL;
+ fk_trigger->transformed = true;
switch (fkconstraint->fk_upd_action)
{
@@ -20919,6 +20925,8 @@ CloneRowTriggersToPartition(Relation parent, Relation partition)
trigStmt->deferrable = trigForm->tgdeferrable;
trigStmt->initdeferred = trigForm->tginitdeferred;
trigStmt->constrrel = NULL; /* passed separately */
+ trigStmt->trigcomment = NULL;
+ trigStmt->transformed = true;
CreateTriggerFiringOn(trigStmt, NULL, RelationGetRelid(partition),
trigForm->tgconstrrelid, InvalidOid, InvalidOid,
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
index d30fda660eb..288291304a0 100644
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -30,6 +30,7 @@
#include "catalog/pg_proc.h"
#include "catalog/pg_trigger.h"
#include "catalog/pg_type.h"
+#include "commands/comment.h"
#include "commands/trigger.h"
#include "executor/executor.h"
#include "miscadmin.h"
@@ -589,13 +590,21 @@ CreateTriggerFiringOn(CreateTrigStmt *stmt, const char *queryString,
false, false);
addNSItemToQuery(pstate, nsitem, false, true, true);
- /* Transform expression. Copy to be sure we don't modify original */
- whenClause = transformWhereClause(pstate,
- copyObject(stmt->whenClause),
- EXPR_KIND_TRIGGER_WHEN,
- "WHEN");
- /* we have to fix its collations too */
- assign_expr_collations(pstate, whenClause);
+ if (stmt->transformed)
+ whenClause = stmt->whenClause;
+ else
+ {
+ /* Transform expression. Copy to be sure we don't modify original */
+ whenClause = transformWhereClause(pstate,
+ copyObject(stmt->whenClause),
+ EXPR_KIND_TRIGGER_WHEN,
+ "WHEN");
+
+ /* we have to fix its collations too */
+ assign_expr_collations(pstate, whenClause);
+
+ stmt->transformed = true;
+ }
/*
* Check for disallowed references to OLD/NEW.
@@ -1204,6 +1213,11 @@ CreateTriggerFiringOn(CreateTrigStmt *stmt, const char *queryString,
/* Keep lock on target rel until end of xact */
table_close(rel, NoLock);
+ /* Add any requested comment */
+ if (stmt->trigcomment != NULL)
+ CreateComments(trigoid, TriggerRelationId, 0,
+ stmt->trigcomment);
+
return myself;
}
@@ -6715,3 +6729,205 @@ check_modified_virtual_generated(TupleDesc tupdesc, HeapTuple tuple)
return tuple;
}
+
+/*
+ * Duplicate the trigger definition for the new relation: Use the source trigger
+ * (source_trigid from source_rel) to initialize a CreateTrigStmt for the target
+ * relation (heapRel).
+ *
+ * Attribute numbers in expression Vars are adjusted according to attmap.
+ */
+CreateTrigStmt *
+generateClonedTriggerStmt(RangeVar *heapRel, Oid source_trigid,
+ Relation source_rel, const AttrMap *attmap)
+{
+ HeapTuple triggerTuple;
+ HeapTuple proctup;
+ Form_pg_trigger trigForm;
+ Form_pg_proc procform;
+ Relation pg_trigger;
+ RangeVar *constrrel = NULL;
+ SysScanDesc tgscan;
+ ScanKeyData skey[1];
+ Datum value;
+ bool isnull;
+ Node *qual = NULL;
+ List *trigargs = NIL;
+ List *cols = NIL;
+ List *funcname = NIL;
+ List *transitionRels = NIL;
+ char *funcschema;
+ CreateTrigStmt *trigStmt;
+
+ pg_trigger = table_open(TriggerRelationId, AccessShareLock);
+
+ /* Find the trigger to copy */
+ ScanKeyInit(&skey[0],
+ Anum_pg_trigger_oid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(source_trigid));
+
+ tgscan = systable_beginscan(pg_trigger, TriggerOidIndexId, true,
+ NULL, 1, skey);
+
+ triggerTuple = systable_getnext(tgscan);
+ if (!HeapTupleIsValid(triggerTuple))
+ elog(ERROR, "could not find tuple for trigger %u", source_trigid);
+
+ trigForm = (Form_pg_trigger) GETSTRUCT(triggerTuple);
+
+ /* Reconstruct trigger function String list */
+ proctup = SearchSysCache1(PROCOID, ObjectIdGetDatum(trigForm->tgfoid));
+ if (!HeapTupleIsValid(proctup))
+ elog(ERROR, "cache lookup failed for function %u", trigForm->tgfoid);
+ procform = (Form_pg_proc) GETSTRUCT(proctup);
+
+ funcschema = get_namespace_name(procform->pronamespace);
+ funcname = list_make2(makeString(funcschema),
+ makeString(pstrdup(NameStr(procform->proname))));
+ ReleaseSysCache(proctup);
+
+ /*
+ * If there is a column list, transform it to a list of column names. Note
+ * we don't need to map this list in any way ...
+ */
+ if (trigForm->tgattr.dim1 > 0)
+ {
+ for (int i = 0; i < trigForm->tgattr.dim1; i++)
+ {
+ Form_pg_attribute col;
+
+ col = TupleDescAttr(RelationGetDescr(source_rel),
+ trigForm->tgattr.values[i] - 1);
+ cols = lappend(cols,
+ makeString(pstrdup(NameStr(col->attname))));
+ }
+ }
+
+ /* Reconstruct trigger arguments list */
+ if (trigForm->tgnargs > 0)
+ {
+ bytea *val;
+ char *p;
+
+ val = DatumGetByteaPP(fastgetattr(triggerTuple,
+ Anum_pg_trigger_tgargs,
+ RelationGetDescr(pg_trigger),
+ &isnull));
+ if (isnull)
+ elog(ERROR, "tgargs is null in trigger \"%s\" for relation \"%s\"",
+ NameStr(trigForm->tgname),
+ RelationGetRelationName(source_rel));
+
+ p = (char *) VARDATA_ANY(val);
+
+ for (int i = 0; i < trigForm->tgnargs; i++)
+ {
+ trigargs = lappend(trigargs, makeString(pstrdup(p)));
+ p += strlen(p) + 1;
+ }
+ }
+
+ /* If the trigger has a WHEN qualification, add that */
+ value = fastgetattr(triggerTuple, Anum_pg_trigger_tgqual,
+ RelationGetDescr(pg_trigger), &isnull);
+ if (!isnull)
+ {
+ bool found_whole_row;
+
+ qual = stringToNode(TextDatumGetCString(value));
+
+ /* Adjust Vars to match new table's column numbering */
+ qual = map_variable_attnos(qual, PRS2_NEW_VARNO, 0,
+ attmap,
+ InvalidOid,
+ &found_whole_row);
+
+ /* As in expandTableLikeClause, reject whole-row variables */
+ if (found_whole_row)
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot convert whole-row table reference"),
+ errdetail("Trigger \"%s\" contains a whole-row table reference.",
+ NameStr(trigForm->tgname)));
+
+ qual = map_variable_attnos(qual, PRS2_OLD_VARNO, 0,
+ attmap,
+ InvalidOid,
+ &found_whole_row);
+
+ /* As in expandTableLikeClause, reject whole-row variables */
+ if (found_whole_row)
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot convert whole-row table reference"),
+ errdetail("Trigger \"%s\" contains a whole-row table reference.",
+ NameStr(trigForm->tgname)));
+ }
+
+ /* Reconstruct trigger old transition table */
+ value = fastgetattr(triggerTuple, Anum_pg_trigger_tgoldtable,
+ RelationGetDescr(pg_trigger), &isnull);
+ if (!isnull)
+ {
+ TriggerTransition *old = makeNode(TriggerTransition);
+
+ old->isNew = false;
+ old->name = pstrdup(NameStr(*DatumGetName(value)));
+ old->isTable = true;
+ transitionRels = lappend(transitionRels, old);
+ }
+
+ /* Reconstruct trigger new transition table */
+ value = fastgetattr(triggerTuple, Anum_pg_trigger_tgnewtable,
+ RelationGetDescr(pg_trigger),
+ &isnull);
+ if (!isnull)
+ {
+ TriggerTransition *new = makeNode(TriggerTransition);
+
+ new->isNew = true;
+ new->name = pstrdup(NameStr(*DatumGetName(value)));
+ new->isTable = true;
+ transitionRels = lappend(transitionRels, new);
+ }
+
+ /* Reconstruct trigger constraint's FROM table */
+ if (OidIsValid(trigForm->tgconstrrelid))
+ {
+ /*
+ * Acquire the AccessShareLock lock on tgconstrrelid now, as it will
+ * be required later in CreateTriggerFiringOn.
+ */
+ LockRelationOid(trigForm->tgconstrrelid, AccessShareLock);
+
+ constrrel =
+ makeRangeVar(get_namespace_name(get_rel_namespace(trigForm->tgconstrrelid)),
+ get_rel_name(trigForm->tgconstrrelid),
+ -1);
+ }
+
+ trigStmt = makeNode(CreateTrigStmt);
+ trigStmt->replace = false;
+ trigStmt->isconstraint = OidIsValid(trigForm->tgconstraint);
+ trigStmt->trigname = pstrdup(NameStr(trigForm->tgname));
+ trigStmt->relation = heapRel;
+ trigStmt->funcname = funcname;
+ trigStmt->args = trigargs;
+ trigStmt->row = TRIGGER_FOR_ROW(trigForm->tgtype);
+ trigStmt->timing = trigForm->tgtype & TRIGGER_TYPE_TIMING_MASK;
+ trigStmt->events = trigForm->tgtype & TRIGGER_TYPE_EVENT_MASK;
+ trigStmt->columns = cols;
+ trigStmt->whenClause = qual;
+ trigStmt->transitionRels = transitionRels;
+ trigStmt->deferrable = trigForm->tgdeferrable;
+ trigStmt->initdeferred = trigForm->tginitdeferred;
+ trigStmt->constrrel = constrrel;
+ trigStmt->trigcomment = NULL;
+ trigStmt->transformed = true;
+
+ systable_endscan(tgscan);
+ table_close(pg_trigger, AccessShareLock);
+
+ return trigStmt;
+}
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 713ee5c10a2..ee9cbbfc6c4 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -786,7 +786,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
TABLE TABLES TABLESAMPLE TABLESPACE TARGET TEMP TEMPLATE TEMPORARY TEXT_P THEN
TIES TIME TIMESTAMP TO TRAILING TRANSACTION TRANSFORM
- TREAT TRIGGER TRIM TRUE_P
+ TREAT TRIGGER TRIGGERS TRIM TRUE_P
TRUNCATE TRUSTED TYPE_P TYPES_P
UESCAPE UNBOUNDED UNCONDITIONAL UNCOMMITTED UNENCRYPTED UNION UNIQUE UNKNOWN
@@ -4296,6 +4296,7 @@ TableLikeOption:
| INDEXES { $$ = CREATE_TABLE_LIKE_INDEXES; }
| STATISTICS { $$ = CREATE_TABLE_LIKE_STATISTICS; }
| STORAGE { $$ = CREATE_TABLE_LIKE_STORAGE; }
+ | TRIGGERS { $$ = CREATE_TABLE_LIKE_TRIGGERS; }
| ALL { $$ = CREATE_TABLE_LIKE_ALL; }
;
@@ -6150,6 +6151,8 @@ CreateTrigStmt:
n->deferrable = false;
n->initdeferred = false;
n->constrrel = NULL;
+ n->trigcomment = NULL;
+ n->transformed = false;
$$ = (Node *) n;
}
| CREATE opt_or_replace CONSTRAINT TRIGGER name AFTER TriggerEvents ON
@@ -6200,6 +6203,8 @@ CreateTrigStmt:
&n->deferrable, &n->initdeferred, &dummy,
NULL, NULL, yyscanner);
n->constrrel = $10;
+ n->trigcomment = NULL;
+ n->transformed = false;
$$ = (Node *) n;
}
;
@@ -18194,6 +18199,7 @@ unreserved_keyword:
| TRANSACTION
| TRANSFORM
| TRIGGER
+ | TRIGGERS
| TRUNCATE
| TRUSTED
| TYPE_P
@@ -18847,6 +18853,7 @@ bare_label_keyword:
| TRANSFORM
| TREAT
| TRIGGER
+ | TRIGGERS
| TRIM
| TRUE_P
| TRUNCATE
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index b5f4c72459d..ca446d5e2d6 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -39,12 +39,14 @@
#include "catalog/pg_opclass.h"
#include "catalog/pg_operator.h"
#include "catalog/pg_statistic_ext.h"
+#include "catalog/pg_trigger.h"
#include "catalog/pg_type.h"
#include "commands/comment.h"
#include "commands/defrem.h"
#include "commands/sequence.h"
#include "commands/tablecmds.h"
#include "commands/tablespace.h"
+#include "commands/trigger.h"
#include "miscadmin.h"
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
@@ -1321,7 +1323,8 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
CREATE_TABLE_LIKE_GENERATED |
CREATE_TABLE_LIKE_CONSTRAINTS |
CREATE_TABLE_LIKE_INDEXES |
- CREATE_TABLE_LIKE_STATISTICS))
+ CREATE_TABLE_LIKE_STATISTICS |
+ CREATE_TABLE_LIKE_TRIGGERS))
{
table_like_clause->relationOid = RelationGetRelid(relation);
cxt->likeclauses = lappend(cxt->likeclauses, table_like_clause);
@@ -1625,6 +1628,38 @@ expandTableLikeClause(RangeVar *heapRel, TableLikeClause *table_like_clause)
list_free(parent_extstats);
}
+ /* Process triggers if required */
+ if ((table_like_clause->options & CREATE_TABLE_LIKE_TRIGGERS) &&
+ relation->trigdesc != NULL)
+ {
+ bool include_comments = (table_like_clause->options & CREATE_TABLE_LIKE_COMMENTS);
+
+ for (int nt = 0; nt < relation->trigdesc->numtriggers; nt++)
+ {
+ Trigger *trig = relation->trigdesc->triggers + nt;
+ Oid trigoid = trig->tgoid;
+ CreateTrigStmt *trig_stmt;
+
+ /* We do not copy internal trigger to the new table */
+ if (trig->tgisinternal)
+ continue;
+
+ trig_stmt = generateClonedTriggerStmt(heapRel, trigoid,
+ relation, attmap);
+
+ /* Copy comment on trigger, if requested */
+ if (include_comments)
+ {
+ comment = GetComment(trigoid, TriggerRelationId, 0);
+
+ /* We make use of CreateTrigStmt's trigcomment option */
+ trig_stmt->trigcomment = comment;
+ }
+
+ result = lappend(result, trig_stmt);
+ }
+ }
+
/* Done with child rel */
table_close(childrel, NoLock);
diff --git a/src/include/commands/trigger.h b/src/include/commands/trigger.h
index b60317c7a75..27a04262acd 100644
--- a/src/include/commands/trigger.h
+++ b/src/include/commands/trigger.h
@@ -266,6 +266,10 @@ extern void AfterTriggerBeginSubXact(void);
extern void AfterTriggerEndSubXact(bool isCommit);
extern void AfterTriggerSetState(ConstraintsSetStmt *stmt);
extern bool AfterTriggerPendingOnRel(Oid relid);
+extern CreateTrigStmt *generateClonedTriggerStmt(RangeVar *heapRel,
+ Oid source_trigid,
+ Relation source_rel,
+ const AttrMap *attmap);
/*
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 646d6ced763..5c3d57982de 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -795,6 +795,7 @@ typedef enum TableLikeOption
CREATE_TABLE_LIKE_INDEXES = 1 << 6,
CREATE_TABLE_LIKE_STATISTICS = 1 << 7,
CREATE_TABLE_LIKE_STORAGE = 1 << 8,
+ CREATE_TABLE_LIKE_TRIGGERS = 1 << 9,
CREATE_TABLE_LIKE_ALL = PG_INT32_MAX
} TableLikeOption;
@@ -3154,6 +3155,9 @@ typedef struct CreateTrigStmt
bool deferrable; /* [NOT] DEFERRABLE */
bool initdeferred; /* INITIALLY {DEFERRED|IMMEDIATE} */
RangeVar *constrrel; /* opposite relation, if RI trigger */
+ char *trigcomment; /* comment to apply to trigger, or NULL */
+ bool transformed; /* true means no need do parse analysis for *
+ * whenClause */
} CreateTrigStmt;
/* ----------------------
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index f7753c5c8a8..00b8803f5aa 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -464,6 +464,7 @@ PG_KEYWORD("transaction", TRANSACTION, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("transform", TRANSFORM, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("treat", TREAT, COL_NAME_KEYWORD, BARE_LABEL)
PG_KEYWORD("trigger", TRIGGER, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("triggers", TRIGGERS, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("trim", TRIM, COL_NAME_KEYWORD, BARE_LABEL)
PG_KEYWORD("true", TRUE_P, RESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("truncate", TRUNCATE, UNRESERVED_KEYWORD, BARE_LABEL)
diff --git a/src/test/regress/expected/create_table_like.out b/src/test/regress/expected/create_table_like.out
index d3c35c14847..0666556e5e6 100644
--- a/src/test/regress/expected/create_table_like.out
+++ b/src/test/regress/expected/create_table_like.out
@@ -631,6 +631,13 @@ Not-null constraints:
"ctl_table_a_not_null" NOT NULL "a"
"ctl_table_d_not_null" NOT NULL "d"
+-- trigger_func was created in triggers.sql
+CREATE TRIGGER trigtest_before_stmt BEFORE DELETE OR UPDATE ON ctl_table
+FOR EACH ROW WHEN (OLD.a > 0)
+EXECUTE PROCEDURE trigger_func('trigtest_before_stmt');
+CREATE TRIGGER trigtest_after_stmt AFTER UPDATE OF a, b ON ctl_table
+FOR EACH STATEMENT
+EXECUTE PROCEDURE trigger_func('trigtest_before_stmt');
-- Test EXCLUDING ALL
CREATE FOREIGN TABLE ctl_foreign_table1(LIKE ctl_table EXCLUDING ALL) SERVER ctl_s0;
\d+ ctl_foreign_table1
@@ -679,6 +686,9 @@ Statistics objects:
Not-null constraints:
"ctl_table_a_not_null" NOT NULL "a"
"ctl_table_d_not_null" NOT NULL "d"
+Triggers:
+ trigtest_after_stmt AFTER UPDATE OF a, b ON ctl_foreign_table2 FOR EACH STATEMENT EXECUTE FUNCTION trigger_func('trigtest_before_stmt')
+ trigtest_before_stmt BEFORE DELETE OR UPDATE ON ctl_foreign_table2 FOR EACH ROW WHEN (old.a > 0) EXECUTE FUNCTION trigger_func('trigtest_before_stmt')
Server: ctl_s0
-- \d+ does not report the value of attcompression for a foreign table, so
diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out
index 1eb8fba0953..1d468ba26d6 100644
--- a/src/test/regress/expected/triggers.out
+++ b/src/test/regress/expected/triggers.out
@@ -165,6 +165,7 @@ drop table trigtest;
CREATE TABLE log_table (tstamp timestamp default timeofday()::timestamp);
CREATE TABLE main_table (a int unique, b int);
COPY main_table (a,b) FROM stdin;
+-- This function also used in create_table_like.sql, don't drop it.
CREATE FUNCTION trigger_func() RETURNS trigger LANGUAGE plpgsql AS '
BEGIN
RAISE NOTICE ''trigger_func(%) called: action = %, when = %, level = %'', TG_ARGV[0], TG_OP, TG_WHEN, TG_LEVEL;
@@ -174,6 +175,20 @@ CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON main_table
FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('before_ins_stmt');
CREATE TRIGGER after_ins_stmt_trig AFTER INSERT ON main_table
FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('after_ins_stmt');
+CREATE TRIGGER wholetrig BEFORE UPDATE ON main_table FOR EACH ROW
+WHEN (OLD IS NOT NULL)
+EXECUTE PROCEDURE trigger_func('modified_a');
+CREATE TABLE main_table1(LIKE main_table INCLUDING TRIGGERS); --error, wholerow reference
+ERROR: cannot convert whole-row table reference
+DETAIL: Trigger "wholetrig" contains a whole-row table reference.
+DROP TRIGGER wholetrig ON main_table;
+CREATE TRIGGER wholetrig BEFORE UPDATE ON main_table FOR EACH ROW
+WHEN (NEW IS NOT NULL)
+EXECUTE PROCEDURE trigger_func('modified_a');
+CREATE TABLE main_table1(LIKE main_table INCLUDING TRIGGERS); --error, wholerow reference
+ERROR: cannot convert whole-row table reference
+DETAIL: Trigger "wholetrig" contains a whole-row table reference.
+DROP TRIGGER wholetrig ON main_table;
--
-- if neither 'FOR EACH ROW' nor 'FOR EACH STATEMENT' was specified,
-- CREATE TRIGGER should default to 'FOR EACH STATEMENT'
@@ -394,6 +409,44 @@ NOTICE: trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER,
NOTICE: trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW
NOTICE: trigger_func(after_upd_b_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT
NOTICE: trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT
+--CREATE TABLE LIKE INCLUDING TRIGGERS tests
+COMMENT ON TRIGGER before_ins_stmt_trig ON main_table IS 'trigger before_ins_stmt_trig';
+CREATE TABLE main_table1(c int, LIKE main_table INCLUDING TRIGGERS INCLUDING COMMENTS);
+SELECT pg_get_triggerdef(oid)
+FROM pg_trigger
+WHERE NOT tgisinternal AND tgrelid IN ('main_table'::regclass, 'main_table1'::regclass)
+ORDER BY tgname, tgrelid::regclass::text COLLATE "C";
+ pg_get_triggerdef
+------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE TRIGGER after_ins_stmt_trig AFTER INSERT ON public.main_table FOR EACH STATEMENT EXECUTE FUNCTION trigger_func('after_ins_stmt')
+ CREATE TRIGGER after_ins_stmt_trig AFTER INSERT ON public.main_table1 FOR EACH STATEMENT EXECUTE FUNCTION trigger_func('after_ins_stmt')
+ CREATE TRIGGER after_upd_a_b_row_trig AFTER UPDATE OF a, b ON public.main_table FOR EACH ROW EXECUTE FUNCTION trigger_func('after_upd_a_b_row')
+ CREATE TRIGGER after_upd_a_b_row_trig AFTER UPDATE OF a, b ON public.main_table1 FOR EACH ROW EXECUTE FUNCTION trigger_func('after_upd_a_b_row')
+ CREATE TRIGGER after_upd_b_row_trig AFTER UPDATE OF b ON public.main_table FOR EACH ROW EXECUTE FUNCTION trigger_func('after_upd_b_row')
+ CREATE TRIGGER after_upd_b_row_trig AFTER UPDATE OF b ON public.main_table1 FOR EACH ROW EXECUTE FUNCTION trigger_func('after_upd_b_row')
+ CREATE TRIGGER after_upd_b_stmt_trig AFTER UPDATE OF b ON public.main_table FOR EACH STATEMENT EXECUTE FUNCTION trigger_func('after_upd_b_stmt')
+ CREATE TRIGGER after_upd_b_stmt_trig AFTER UPDATE OF b ON public.main_table1 FOR EACH STATEMENT EXECUTE FUNCTION trigger_func('after_upd_b_stmt')
+ CREATE TRIGGER after_upd_stmt_trig AFTER UPDATE ON public.main_table FOR EACH STATEMENT EXECUTE FUNCTION trigger_func('after_upd_stmt')
+ CREATE TRIGGER after_upd_stmt_trig AFTER UPDATE ON public.main_table1 FOR EACH STATEMENT EXECUTE FUNCTION trigger_func('after_upd_stmt')
+ CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON public.main_table FOR EACH STATEMENT EXECUTE FUNCTION trigger_func('before_ins_stmt')
+ CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON public.main_table1 FOR EACH STATEMENT EXECUTE FUNCTION trigger_func('before_ins_stmt')
+ CREATE TRIGGER before_upd_a_row_trig BEFORE UPDATE OF a ON public.main_table FOR EACH ROW EXECUTE FUNCTION trigger_func('before_upd_a_row')
+ CREATE TRIGGER before_upd_a_row_trig BEFORE UPDATE OF a ON public.main_table1 FOR EACH ROW EXECUTE FUNCTION trigger_func('before_upd_a_row')
+ CREATE TRIGGER before_upd_a_stmt_trig BEFORE UPDATE OF a ON public.main_table FOR EACH STATEMENT EXECUTE FUNCTION trigger_func('before_upd_a_stmt')
+ CREATE TRIGGER before_upd_a_stmt_trig BEFORE UPDATE OF a ON public.main_table1 FOR EACH STATEMENT EXECUTE FUNCTION trigger_func('before_upd_a_stmt')
+(16 rows)
+
+SELECT pc.relname, pd.tgname, obj_description(pd.oid, 'pg_trigger')
+FROM pg_trigger pd JOIN pg_class pc
+ON pc.oid = pd.tgrelid AND pd.tgname = 'before_ins_stmt_trig'
+ORDER BY 1;
+ relname | tgname | obj_description
+-------------+----------------------+------------------------------
+ main_table | before_ins_stmt_trig | trigger before_ins_stmt_trig
+ main_table1 | before_ins_stmt_trig | trigger before_ins_stmt_trig
+(2 rows)
+
+COMMENT ON TRIGGER before_ins_stmt_trig ON main_table IS NULL;
--
-- Test case for bug with BEFORE trigger followed by AFTER trigger with WHEN
--
@@ -422,6 +475,20 @@ NOTICE: dummy_update_func(afterb) called: action = UPDATE, old = (t), new = (f)
UPDATE some_t SET some_col = TRUE;
NOTICE: dummy_update_func(before) called: action = UPDATE, old = (f), new = (t)
NOTICE: dummy_update_func(aftera) called: action = UPDATE, old = (f), new = (t)
+CREATE TABLE some_t1 (c INT, LIKE some_t INCLUDING TRIGGERS INCLUDING COMMENTS);
+\d+ some_t1
+ Table "public.some_t1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+----------+---------+-----------+----------+---------+---------+--------------+-------------
+ c | integer | | | | plain | |
+ some_col | boolean | | not null | | plain | |
+Not-null constraints:
+ "some_t_some_col_not_null" NOT NULL "some_col"
+Triggers:
+ some_trig_aftera AFTER UPDATE ON some_t1 FOR EACH ROW WHEN (NOT old.some_col AND new.some_col) EXECUTE FUNCTION dummy_update_func('aftera')
+ some_trig_afterb AFTER UPDATE ON some_t1 FOR EACH ROW WHEN (NOT new.some_col) EXECUTE FUNCTION dummy_update_func('afterb')
+ some_trig_before BEFORE UPDATE ON some_t1 FOR EACH ROW EXECUTE FUNCTION dummy_update_func('before')
+
DROP TABLE some_t;
-- bogus cases
CREATE TRIGGER error_upd_and_col BEFORE UPDATE OR UPDATE OF a ON main_table
@@ -861,6 +928,9 @@ CREATE TRIGGER instead_of_update_trig INSTEAD OF UPDATE ON main_view
FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_upd');
CREATE TRIGGER instead_of_delete_trig INSTEAD OF DELETE ON main_view
FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_del');
+CREATE TABLE main_view_table(LIKE main_view INCLUDING TRIGGERS); --error
+ERROR: "main_view_table" is a table
+DETAIL: Tables cannot have INSTEAD OF triggers.
-- Valid BEFORE statement VIEW triggers
CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON main_view
FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_ins_stmt');
@@ -1769,9 +1839,21 @@ create trigger my_trigger after update on my_view referencing old table as old_t
for each statement execute procedure my_trigger_function();
ERROR: "my_view" is a view
DETAIL: Triggers on views cannot have transition tables.
-drop function my_trigger_function();
+create trigger my_trigger1 before update of i on my_view
+ for each statement execute procedure my_trigger_function();
+create table my_view_copy(LIKE my_view including triggers);
+\d my_view_copy
+ Table "public.my_view_copy"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ i | integer | | |
+Triggers:
+ my_trigger1 BEFORE UPDATE OF i ON my_view_copy FOR EACH STATEMENT EXECUTE FUNCTION my_trigger_function()
+
drop view my_view;
drop table my_table;
+drop table my_view_copy;
+drop function my_trigger_function();
--
-- Verify cases that are unsupported with partitioned tables
--
@@ -2318,6 +2400,20 @@ create constraint trigger parted_trig_two after insert on parted_constr
deferrable initially deferred enforced
for each row when (bark(new.b) AND new.a % 2 = 1)
execute procedure trigger_notice_ab();
+create table parted_constr_copy (like parted_constr including all);
+select pg_get_triggerdef(oid)
+from pg_trigger
+where not tgisinternal and tgrelid in ('parted_constr_copy'::regclass, 'parted_constr'::regclass)
+order by tgname;
+ pg_get_triggerdef
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE CONSTRAINT TRIGGER parted_trig AFTER INSERT ON public.parted_constr DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE FUNCTION trigger_notice_ab()
+ CREATE CONSTRAINT TRIGGER parted_trig AFTER INSERT ON public.parted_constr_copy DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE FUNCTION trigger_notice_ab()
+ CREATE CONSTRAINT TRIGGER parted_trig_two AFTER INSERT ON public.parted_constr DEFERRABLE INITIALLY DEFERRED FOR EACH ROW WHEN ((bark(new.b) AND ((new.a % 2) = 1))) EXECUTE FUNCTION trigger_notice_ab()
+ CREATE CONSTRAINT TRIGGER parted_trig_two AFTER INSERT ON public.parted_constr_copy DEFERRABLE INITIALLY DEFERRED FOR EACH ROW WHEN ((bark(new.b) AND ((new.a % 2) = 1))) EXECUTE FUNCTION trigger_notice_ab()
+(4 rows)
+
+drop table parted_constr_copy;
-- The immediate constraint is fired immediately; the WHEN clause of the
-- deferred constraint is also called immediately. The deferred constraint
-- is fired at commit time.
@@ -3092,6 +3188,18 @@ create trigger iocdu_tt_parted_insert_trig
create trigger iocdu_tt_parted_update_trig
after update on iocdu_tt_parted referencing old table as old_table new table as new_table
for each statement execute procedure dump_update();
+CREATE TABLE iocdu_tt_parted_copy(LIKE iocdu_tt_parted INCLUDING TRIGGERS);
+\d iocdu_tt_parted_copy
+ Table "public.iocdu_tt_parted_copy"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | not null |
+ b | text | | |
+Triggers:
+ iocdu_tt_parted_insert_trig AFTER INSERT ON iocdu_tt_parted_copy REFERENCING NEW TABLE AS new_table FOR EACH STATEMENT EXECUTE FUNCTION dump_insert()
+ iocdu_tt_parted_update_trig AFTER UPDATE ON iocdu_tt_parted_copy REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table FOR EACH STATEMENT EXECUTE FUNCTION dump_update()
+
+DROP TABLE iocdu_tt_parted_copy;
-- inserts only
insert into iocdu_tt_parted values (1, 'AAA'), (2, 'BBB')
on conflict (a) do
@@ -3574,6 +3682,26 @@ begin
end;
$$;
alter function whoami() owner to regress_fn_owner;
+-- CREATE TABLE LIKE INCLUDING TRIGGERS for constraint trigger
+create schema test_trig;
+create table test_trig.trig_t1 (id integer);
+create table test_trig.trig_t2 (id integer);
+alter function whoami() set schema test_trig;
+create constraint trigger con_trig_test after insert on test_trig.trig_t1
+from test_trig.trig_t2
+deferrable initially deferred
+for each row
+execute function test_trig.whoami();
+create table trig_t1_copy(like test_trig.trig_t1 including triggers);
+select pg_get_triggerdef(oid) from pg_trigger where tgrelid = 'trig_t1_copy'::regclass;
+ pg_get_triggerdef
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE CONSTRAINT TRIGGER con_trig_test AFTER INSERT ON public.trig_t1_copy FROM test_trig.trig_t2 DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION test_trig.whoami()
+(1 row)
+
+alter function test_trig.whoami() set schema public;
+drop table test_trig.trig_t1, test_trig.trig_t2, trig_t1_copy;
+drop schema test_trig;
create table defer_trig (id integer);
grant insert on defer_trig to public;
create constraint trigger whoami after insert on defer_trig
diff --git a/src/test/regress/sql/create_table_like.sql b/src/test/regress/sql/create_table_like.sql
index 93389b57dbf..7457e7d961e 100644
--- a/src/test/regress/sql/create_table_like.sql
+++ b/src/test/regress/sql/create_table_like.sql
@@ -258,6 +258,13 @@ ALTER TABLE ctl_table ADD CONSTRAINT foo CHECK (b = 'text');
ALTER TABLE ctl_table ALTER COLUMN b SET STORAGE MAIN;
\d+ ctl_table
+-- trigger_func was created in triggers.sql
+CREATE TRIGGER trigtest_before_stmt BEFORE DELETE OR UPDATE ON ctl_table
+FOR EACH ROW WHEN (OLD.a > 0)
+EXECUTE PROCEDURE trigger_func('trigtest_before_stmt');
+CREATE TRIGGER trigtest_after_stmt AFTER UPDATE OF a, b ON ctl_table
+FOR EACH STATEMENT
+EXECUTE PROCEDURE trigger_func('trigtest_before_stmt');
-- Test EXCLUDING ALL
CREATE FOREIGN TABLE ctl_foreign_table1(LIKE ctl_table EXCLUDING ALL) SERVER ctl_s0;
diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sql
index 5f7f75d7ba5..a1e9dbba8bd 100644
--- a/src/test/regress/sql/triggers.sql
+++ b/src/test/regress/sql/triggers.sql
@@ -108,6 +108,7 @@ COPY main_table (a,b) FROM stdin;
80 15
\.
+-- This function also used in create_table_like.sql, don't drop it.
CREATE FUNCTION trigger_func() RETURNS trigger LANGUAGE plpgsql AS '
BEGIN
RAISE NOTICE ''trigger_func(%) called: action = %, when = %, level = %'', TG_ARGV[0], TG_OP, TG_WHEN, TG_LEVEL;
@@ -120,6 +121,18 @@ FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('before_ins_stmt');
CREATE TRIGGER after_ins_stmt_trig AFTER INSERT ON main_table
FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('after_ins_stmt');
+CREATE TRIGGER wholetrig BEFORE UPDATE ON main_table FOR EACH ROW
+WHEN (OLD IS NOT NULL)
+EXECUTE PROCEDURE trigger_func('modified_a');
+CREATE TABLE main_table1(LIKE main_table INCLUDING TRIGGERS); --error, wholerow reference
+DROP TRIGGER wholetrig ON main_table;
+
+CREATE TRIGGER wholetrig BEFORE UPDATE ON main_table FOR EACH ROW
+WHEN (NEW IS NOT NULL)
+EXECUTE PROCEDURE trigger_func('modified_a');
+CREATE TABLE main_table1(LIKE main_table INCLUDING TRIGGERS); --error, wholerow reference
+DROP TRIGGER wholetrig ON main_table;
+
--
-- if neither 'FOR EACH ROW' nor 'FOR EACH STATEMENT' was specified,
-- CREATE TRIGGER should default to 'FOR EACH STATEMENT'
@@ -234,6 +247,19 @@ SELECT pg_get_triggerdef(oid) FROM pg_trigger WHERE tgrelid = 'main_table'::regc
UPDATE main_table SET a = 50;
UPDATE main_table SET b = 10;
+--CREATE TABLE LIKE INCLUDING TRIGGERS tests
+COMMENT ON TRIGGER before_ins_stmt_trig ON main_table IS 'trigger before_ins_stmt_trig';
+CREATE TABLE main_table1(c int, LIKE main_table INCLUDING TRIGGERS INCLUDING COMMENTS);
+SELECT pg_get_triggerdef(oid)
+FROM pg_trigger
+WHERE NOT tgisinternal AND tgrelid IN ('main_table'::regclass, 'main_table1'::regclass)
+ORDER BY tgname, tgrelid::regclass::text COLLATE "C";
+SELECT pc.relname, pd.tgname, obj_description(pd.oid, 'pg_trigger')
+FROM pg_trigger pd JOIN pg_class pc
+ON pc.oid = pd.tgrelid AND pd.tgname = 'before_ins_stmt_trig'
+ORDER BY 1;
+COMMENT ON TRIGGER before_ins_stmt_trig ON main_table IS NULL;
+
--
-- Test case for bug with BEFORE trigger followed by AFTER trigger with WHEN
--
@@ -258,6 +284,8 @@ INSERT INTO some_t VALUES (TRUE);
UPDATE some_t SET some_col = TRUE;
UPDATE some_t SET some_col = FALSE;
UPDATE some_t SET some_col = TRUE;
+CREATE TABLE some_t1 (c INT, LIKE some_t INCLUDING TRIGGERS INCLUDING COMMENTS);
+\d+ some_t1
DROP TABLE some_t;
-- bogus cases
@@ -621,6 +649,8 @@ FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_upd');
CREATE TRIGGER instead_of_delete_trig INSTEAD OF DELETE ON main_view
FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_del');
+CREATE TABLE main_view_table(LIKE main_view INCLUDING TRIGGERS); --error
+
-- Valid BEFORE statement VIEW triggers
CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON main_view
FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_ins_stmt');
@@ -1214,9 +1244,14 @@ create view my_view as select * from my_table;
create function my_trigger_function() returns trigger as $$ begin end; $$ language plpgsql;
create trigger my_trigger after update on my_view referencing old table as old_table
for each statement execute procedure my_trigger_function();
-drop function my_trigger_function();
+create trigger my_trigger1 before update of i on my_view
+ for each statement execute procedure my_trigger_function();
+create table my_view_copy(LIKE my_view including triggers);
+\d my_view_copy
drop view my_view;
drop table my_table;
+drop table my_view_copy;
+drop function my_trigger_function();
--
-- Verify cases that are unsupported with partitioned tables
@@ -1608,6 +1643,13 @@ create constraint trigger parted_trig_two after insert on parted_constr
for each row when (bark(new.b) AND new.a % 2 = 1)
execute procedure trigger_notice_ab();
+create table parted_constr_copy (like parted_constr including all);
+select pg_get_triggerdef(oid)
+from pg_trigger
+where not tgisinternal and tgrelid in ('parted_constr_copy'::regclass, 'parted_constr'::regclass)
+order by tgname;
+drop table parted_constr_copy;
+
-- The immediate constraint is fired immediately; the WHEN clause of the
-- deferred constraint is also called immediately. The deferred constraint
-- is fired at commit time.
@@ -2278,6 +2320,10 @@ create trigger iocdu_tt_parted_update_trig
after update on iocdu_tt_parted referencing old table as old_table new table as new_table
for each statement execute procedure dump_update();
+CREATE TABLE iocdu_tt_parted_copy(LIKE iocdu_tt_parted INCLUDING TRIGGERS);
+\d iocdu_tt_parted_copy
+DROP TABLE iocdu_tt_parted_copy;
+
-- inserts only
insert into iocdu_tt_parted values (1, 'AAA'), (2, 'BBB')
on conflict (a) do
@@ -2735,6 +2781,25 @@ end;
$$;
alter function whoami() owner to regress_fn_owner;
+-- CREATE TABLE LIKE INCLUDING TRIGGERS for constraint trigger
+create schema test_trig;
+create table test_trig.trig_t1 (id integer);
+create table test_trig.trig_t2 (id integer);
+alter function whoami() set schema test_trig;
+
+create constraint trigger con_trig_test after insert on test_trig.trig_t1
+from test_trig.trig_t2
+deferrable initially deferred
+for each row
+execute function test_trig.whoami();
+
+create table trig_t1_copy(like test_trig.trig_t1 including triggers);
+select pg_get_triggerdef(oid) from pg_trigger where tgrelid = 'trig_t1_copy'::regclass;
+
+alter function test_trig.whoami() set schema public;
+drop table test_trig.trig_t1, test_trig.trig_t2, trig_t1_copy;
+drop schema test_trig;
+
create table defer_trig (id integer);
grant insert on defer_trig to public;
create constraint trigger whoami after insert on defer_trig
--
2.34.1
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: CREATE TABLE LIKE INCLUDING TRIGGERS
2026-01-16 07:34 Re: CREATE TABLE LIKE INCLUDING TRIGGERS jian he <[email protected]>
@ 2026-01-16 12:13 ` Andrey Borodin <[email protected]>
1 sibling, 0 replies; 12+ messages in thread
From: Andrey Borodin @ 2026-01-16 12:13 UTC (permalink / raw)
To: jian he <[email protected]>; +Cc: pgsql-hackers
> On 16 Jan 2026, at 12:34, jian he <[email protected]> wrote:
IMO the patch is ready for committer.
Best regards, Andrey Borodin.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: CREATE TABLE LIKE INCLUDING TRIGGERS
2026-01-16 07:34 Re: CREATE TABLE LIKE INCLUDING TRIGGERS jian he <[email protected]>
@ 2026-01-21 12:13 ` Zsolt Parragi <[email protected]>
2026-01-22 06:08 ` Re: CREATE TABLE LIKE INCLUDING TRIGGERS jian he <[email protected]>
1 sibling, 1 reply; 12+ messages in thread
From: Zsolt Parragi @ 2026-01-21 12:13 UTC (permalink / raw)
To: jian he <[email protected]>; +Cc: Andrey Borodin <[email protected]>; pgsql-hackers
Hello!
I tested the patch, it works as described, but I did notice one possible issue:
Shouldn't this preserve the enabled state of the triggers, or if it
doesn't, should the documentation include this limitations?
Currently the new table will always use the default
TRIGGER_FIRES_ON_ORIGIN, regardless how it is configured for the
original table.
diff --git a/src/test/regress/sql/triggers.sql
b/src/test/regress/sql/triggers.sql
index a1e9dbba8bd..24f7bfb5837 100644
--- a/src/test/regress/sql/triggers.sql
+++ b/src/test/regress/sql/triggers.sql
@@ -260,6 +260,18 @@ ON pc.oid = pd.tgrelid AND pd.tgname =
'before_ins_stmt_trig'
ORDER BY 1;
COMMENT ON TRIGGER before_ins_stmt_trig ON main_table IS NULL;
+-- Test that trigger firing state is not preserved by LIKE INCLUDING TRIGGERS
+ALTER TABLE main_table DISABLE TRIGGER before_ins_stmt_trig;
+CREATE TABLE main_table2 (LIKE main_table INCLUDING TRIGGERS);
+SELECT c.relname, t.tgname, t.tgenabled
+FROM pg_trigger t
+JOIN pg_class c ON t.tgrelid = c.oid
+WHERE t.tgname = 'before_ins_stmt_trig'
+ AND c.relname IN ('main_table', 'main_table2')
+ORDER BY c.relname;
+DROP TABLE main_table2;
+ALTER TABLE main_table ENABLE TRIGGER before_ins_stmt_trig;
+
--
-- Test case for bug with BEFORE trigger followed by AFTER trigger with WHEN
--
On Wed, Jan 21, 2026 at 12:00 PM jian he <[email protected]> wrote:
>
> On Fri, Jan 2, 2026 at 5:25 PM Andrey Borodin <[email protected]> wrote:
> >
> > + char *trigcomment; /* comment to apply to trigger, or NULL */
> > No other Create*Stmt has a comment field. Comments seem to be handled through separate CommentStmt creation.
> >
>
> See CreateStatsStmt.stxcomment, IndexStmt.idxcomment.
> We need CreateTrigStmt.trigcomment, because if INCLUDING COMMENTS is specified,
> CreateTrigStmt.trigcomment can be used to hold the source object's comments.
>
> > Some nitpicking about tests:
> > 1. INSTEAD OF triggers on views - The error is tested, but should also test that statement-level VIEW triggers work
> ok. test added.
>
> > 2. Triggers on partitioned tables - What happens when you LIKE a partitioned table? Are partition triggers cloned?
> no. only the trigger on the partitioned table itself will be cloned.
> see tests ``create table parted_constr_copy (like parted_constr
> including all);``
>
> > 3. Cross-schema trigger functions - The function name reconstruction handles schemas, but is it tested?
> >
> ok. test added.
>
> > + funcname = list_make2(makeString(schemaname),makeString(NameStr(procform->proname)));
> > Other NameStr() are pstrdup()'d, maybe let's pstrdup() this too?
> >
> ok.
>
> > + /* Reconstruct trigger old transition table */
> > Second instance of this comment is wrong.
> >
> ok.
>
> > + PG_KEYWORD("triggers", TRIGGERS, UNRESERVED_KEYWORD, BARE_LABEL)
> > Won't this break some user SQLs?
> it's marked as an un-reserved word, so it won't break any SQL, i think.
>
> v4-0001, v4-0002 was removed, as Robert said in [1], now I am using
> the same ugly
> hack consistently, now code is more aligned with INCLUDING INDEXES, INCLUDING
> STATISTICS.
>
> pstrdup have been used in more places in generateClonedTriggerStmt.
>
>
>
> --
> jian
> https://url.avanan.click/v2/r01/___https://www.enterprisedb.com/___.YXAzOnBlcmNvbmE6YTpnOjE5NWMwMWNm...
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: CREATE TABLE LIKE INCLUDING TRIGGERS
2026-01-16 07:34 Re: CREATE TABLE LIKE INCLUDING TRIGGERS jian he <[email protected]>
2026-01-21 12:13 ` Re: CREATE TABLE LIKE INCLUDING TRIGGERS Zsolt Parragi <[email protected]>
@ 2026-01-22 06:08 ` jian he <[email protected]>
2026-01-22 14:20 ` Re: CREATE TABLE LIKE INCLUDING TRIGGERS Zsolt Parragi <[email protected]>
0 siblings, 1 reply; 12+ messages in thread
From: jian he @ 2026-01-22 06:08 UTC (permalink / raw)
To: Zsolt Parragi <[email protected]>; +Cc: Andrey Borodin <[email protected]>; pgsql-hackers
On Wed, Jan 21, 2026 at 8:13 PM Zsolt Parragi <[email protected]> wrote:
>
> Hello!
>
> I tested the patch, it works as described, but I did notice one possible issue:
>
> Shouldn't this preserve the enabled state of the triggers, or if it
> doesn't, should the documentation include this limitations?
>
I intended to document it as
<para>
All non-internal triggers on the original table will be
created on the new table.
By default, these triggers fire in "origin" and "local" modes. Refer
to <link linkend="sql-altertable"><command>ALTER
TABLE</command></link>
to modify the firing condition.
</para>
what do you think?
--
jian
https://www.enterprisedb.com/
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: CREATE TABLE LIKE INCLUDING TRIGGERS
2026-01-16 07:34 Re: CREATE TABLE LIKE INCLUDING TRIGGERS jian he <[email protected]>
2026-01-21 12:13 ` Re: CREATE TABLE LIKE INCLUDING TRIGGERS Zsolt Parragi <[email protected]>
2026-01-22 06:08 ` Re: CREATE TABLE LIKE INCLUDING TRIGGERS jian he <[email protected]>
@ 2026-01-22 14:20 ` Zsolt Parragi <[email protected]>
2026-02-05 06:15 ` Re: CREATE TABLE LIKE INCLUDING TRIGGERS jian he <[email protected]>
0 siblings, 1 reply; 12+ messages in thread
From: Zsolt Parragi @ 2026-01-22 14:20 UTC (permalink / raw)
To: jian he <[email protected]>; +Cc: Andrey Borodin <[email protected]>; pgsql-hackers
> > Shouldn't this preserve the enabled state of the triggers, or if it
> > doesn't, should the documentation include this limitations?
> >
>
> I intended to document it as ...
After looking into this a bit more, I am more on the side of copying
this setting properly.
The already existing INCLUDING CONSTRAINTS copies the constraints,
including their enabled/disabled status, correctly marking them
disabled if a CHECK constraint is defined but not enforced. Wouldn't
it be strange for INCLUDING TRIGGERS to work differently?
From the test suite:
CREATE TABLE ctlt1_inh (LIKE ctlt1 INCLUDING CONSTRAINTS INCLUDING
COMMENTS) INHERITS (ctlt1);
\d+ ctlt1_inh
Table "public.ctlt1_inh"
Column | Type | Collation | Nullable | Default | Storage | Stats
target | Description
--------+------+-----------+----------+---------+----------+--------------+-------------
a | text | | not null | | main | | A
b | text | | | | extended | | B
Check constraints:
"cc" CHECK (length(b) > 100)
"ctlt1_a_check" CHECK (length(a) > 2)
"ctlt1_b_check" CHECK (length(b) > 100) NOT ENFORCED
Not-null constraints:
"ctlt1_a_not_null" NOT NULL "a" (local, inherited)
Inherits: ctlt1
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: CREATE TABLE LIKE INCLUDING TRIGGERS
2026-01-16 07:34 Re: CREATE TABLE LIKE INCLUDING TRIGGERS jian he <[email protected]>
2026-01-21 12:13 ` Re: CREATE TABLE LIKE INCLUDING TRIGGERS Zsolt Parragi <[email protected]>
2026-01-22 06:08 ` Re: CREATE TABLE LIKE INCLUDING TRIGGERS jian he <[email protected]>
2026-01-22 14:20 ` Re: CREATE TABLE LIKE INCLUDING TRIGGERS Zsolt Parragi <[email protected]>
@ 2026-02-05 06:15 ` jian he <[email protected]>
2026-02-06 14:49 ` Re: CREATE TABLE LIKE INCLUDING TRIGGERS Zsolt Parragi <[email protected]>
0 siblings, 1 reply; 12+ messages in thread
From: jian he @ 2026-02-05 06:15 UTC (permalink / raw)
To: Zsolt Parragi <[email protected]>; +Cc: Andrey Borodin <[email protected]>; pgsql-hackers
On Thu, Jan 22, 2026 at 10:20 PM Zsolt Parragi
<[email protected]> wrote:
>
> After looking into this a bit more, I am more on the side of copying
> this setting properly.
>
> The already existing INCLUDING CONSTRAINTS copies the constraints,
> including their enabled/disabled status, correctly marking them
> disabled if a CHECK constraint is defined but not enforced. Wouldn't
> it be strange for INCLUDING TRIGGERS to work differently?
>
hi.
please check the attached.
v6-0001 is the same as v5-0001.
v6-0002 ensures that CREATE TABLE ... LIKE ... INCLUDING TRIGGERS
correctly copies the tgenabled status from the source table's triggers
to the new table
--
jian
https://www.enterprisedb.com/
Attachments:
[text/x-patch] v6-0002-create-table-like-including-triggers-copies-tgenabled.patch (7.8K, 2-v6-0002-create-table-like-including-triggers-copies-tgenabled.patch)
download | inline diff:
From 67689c9284e6077d69b9dd5d8147d307b80c6821 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Thu, 5 Feb 2026 13:59:39 +0800
Subject: [PATCH v6 2/2] create table like including triggers copies tgenabled
discussion: https://postgr.es/m/CACJufxHJAr2FjbeB6ghg_-N5dxX5JVnjKSLOUxOyt4TeaAWQkg@mail.gmail.com
commitfest: https://commitfest.postgresql.org/patch/6087
---
src/backend/catalog/index.c | 1 +
src/backend/commands/tablecmds.c | 4 ++++
src/backend/commands/trigger.c | 1 +
src/backend/parser/gram.y | 2 ++
src/backend/tcop/utility.c | 12 ++++++++----
src/include/nodes/parsenodes.h | 1 +
src/test/regress/expected/triggers.out | 9 +++++++--
src/test/regress/sql/triggers.sql | 3 +++
8 files changed, 27 insertions(+), 6 deletions(-)
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index b0b5a886fa1..d4570626e0b 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -2021,6 +2021,7 @@ index_constraint_create(Relation heapRelation,
CreateTrigStmt *trigger = makeNode(CreateTrigStmt);
trigger->replace = false;
+ trigger->tgenabled = TRIGGER_FIRES_ON_ORIGIN;
trigger->isconstraint = true;
trigger->trigname = (constraintType == CONSTRAINT_PRIMARY) ?
"PK_ConstraintTrigger" :
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 6a25f6b7553..6c53df1e54a 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -13839,6 +13839,7 @@ CreateFKCheckTrigger(Oid myRelOid, Oid refRelOid, Constraint *fkconstraint,
*/
fk_trigger = makeNode(CreateTrigStmt);
fk_trigger->replace = false;
+ fk_trigger->tgenabled = TRIGGER_FIRES_ON_ORIGIN;
fk_trigger->isconstraint = true;
fk_trigger->trigname = "RI_ConstraintTrigger_c";
fk_trigger->relation = NULL;
@@ -13900,6 +13901,7 @@ createForeignKeyActionTriggers(Oid myRelOid, Oid refRelOid, Constraint *fkconstr
*/
fk_trigger = makeNode(CreateTrigStmt);
fk_trigger->replace = false;
+ fk_trigger->tgenabled = TRIGGER_FIRES_ON_ORIGIN;
fk_trigger->isconstraint = true;
fk_trigger->trigname = "RI_ConstraintTrigger_a";
fk_trigger->relation = NULL;
@@ -13962,6 +13964,7 @@ createForeignKeyActionTriggers(Oid myRelOid, Oid refRelOid, Constraint *fkconstr
*/
fk_trigger = makeNode(CreateTrigStmt);
fk_trigger->replace = false;
+ fk_trigger->tgenabled = TRIGGER_FIRES_ON_ORIGIN;
fk_trigger->isconstraint = true;
fk_trigger->trigname = "RI_ConstraintTrigger_a";
fk_trigger->relation = NULL;
@@ -20911,6 +20914,7 @@ CloneRowTriggersToPartition(Relation parent, Relation partition)
trigStmt = makeNode(CreateTrigStmt);
trigStmt->replace = false;
+ trigStmt->tgenabled = trigForm->tgenabled;
trigStmt->isconstraint = OidIsValid(trigForm->tgconstraint);
trigStmt->trigname = NameStr(trigForm->tgname);
trigStmt->relation = NULL;
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
index e944265bd9f..280427559d4 100644
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -6947,6 +6947,7 @@ generateClonedTriggerStmt(RangeVar *heapRel, Oid source_trigid,
trigStmt = makeNode(CreateTrigStmt);
trigStmt->replace = false;
+ trigStmt->tgenabled = trigForm->tgenabled;
trigStmt->isconstraint = OidIsValid(trigForm->tgconstraint);
trigStmt->trigname = pstrdup(NameStr(trigForm->tgname));
trigStmt->relation = heapRel;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index ee9cbbfc6c4..24b27d2c47d 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -6137,6 +6137,7 @@ CreateTrigStmt:
CreateTrigStmt *n = makeNode(CreateTrigStmt);
n->replace = $2;
+ n->tgenabled = TRIGGER_FIRES_ON_ORIGIN;
n->isconstraint = false;
n->trigname = $4;
n->relation = $8;
@@ -6188,6 +6189,7 @@ CreateTrigStmt:
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("CREATE OR REPLACE CONSTRAINT TRIGGER is not supported"),
parser_errposition(@1)));
+ n->tgenabled = TRIGGER_FIRES_ON_ORIGIN;
n->isconstraint = true;
n->trigname = $5;
n->relation = $9;
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index 34dd6e18df5..d240ecd4077 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -1701,10 +1701,14 @@ ProcessUtilitySlow(ParseState *pstate,
break;
case T_CreateTrigStmt:
- address = CreateTrigger((CreateTrigStmt *) parsetree,
- queryString, InvalidOid, InvalidOid,
- InvalidOid, InvalidOid, InvalidOid,
- InvalidOid, NULL, false, false);
+ {
+ CreateTrigStmt *stmt = (CreateTrigStmt *) parsetree;
+
+ address = CreateTriggerFiringOn(stmt, queryString, InvalidOid, InvalidOid,
+ InvalidOid, InvalidOid, InvalidOid,
+ InvalidOid, NULL, false,
+ false, stmt->tgenabled);
+ }
break;
case T_CreatePLangStmt:
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 5c3d57982de..69475beffaf 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -3137,6 +3137,7 @@ typedef struct CreateTrigStmt
{
NodeTag type;
bool replace; /* replace trigger if already exists */
+ char tgenabled; /* trigger's firing configuration */
bool isconstraint; /* This is a constraint trigger */
char *trigname; /* TRIGGER's name */
RangeVar *relation; /* relation trigger is on */
diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out
index 7e5413050be..6e658791892 100644
--- a/src/test/regress/expected/triggers.out
+++ b/src/test/regress/expected/triggers.out
@@ -475,6 +475,9 @@ NOTICE: dummy_update_func(afterb) called: action = UPDATE, old = (t), new = (f)
UPDATE some_t SET some_col = TRUE;
NOTICE: dummy_update_func(before) called: action = UPDATE, old = (f), new = (t)
NOTICE: dummy_update_func(aftera) called: action = UPDATE, old = (f), new = (t)
+ALTER TABLE some_t ENABLE REPLICA TRIGGER some_trig_aftera;
+ALTER TABLE some_t DISABLE TRIGGER some_trig_afterb;
+ALTER TABLE some_t ENABLE ALWAYS TRIGGER some_trig_before;
CREATE TABLE some_t1 (c INT, LIKE some_t INCLUDING TRIGGERS INCLUDING COMMENTS);
\d+ some_t1
Table "public.some_t1"
@@ -484,10 +487,12 @@ CREATE TABLE some_t1 (c INT, LIKE some_t INCLUDING TRIGGERS INCLUDING COMMENTS);
some_col | boolean | | not null | | plain | |
Not-null constraints:
"some_t_some_col_not_null" NOT NULL "some_col"
-Triggers:
- some_trig_aftera AFTER UPDATE ON some_t1 FOR EACH ROW WHEN (NOT old.some_col AND new.some_col) EXECUTE FUNCTION dummy_update_func('aftera')
+Disabled user triggers:
some_trig_afterb AFTER UPDATE ON some_t1 FOR EACH ROW WHEN (NOT new.some_col) EXECUTE FUNCTION dummy_update_func('afterb')
+Triggers firing always:
some_trig_before BEFORE UPDATE ON some_t1 FOR EACH ROW EXECUTE FUNCTION dummy_update_func('before')
+Triggers firing on replica only:
+ some_trig_aftera AFTER UPDATE ON some_t1 FOR EACH ROW WHEN (NOT old.some_col AND new.some_col) EXECUTE FUNCTION dummy_update_func('aftera')
DROP TABLE some_t;
-- bogus cases
diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sql
index 064dc22314c..8169400216f 100644
--- a/src/test/regress/sql/triggers.sql
+++ b/src/test/regress/sql/triggers.sql
@@ -284,6 +284,9 @@ INSERT INTO some_t VALUES (TRUE);
UPDATE some_t SET some_col = TRUE;
UPDATE some_t SET some_col = FALSE;
UPDATE some_t SET some_col = TRUE;
+ALTER TABLE some_t ENABLE REPLICA TRIGGER some_trig_aftera;
+ALTER TABLE some_t DISABLE TRIGGER some_trig_afterb;
+ALTER TABLE some_t ENABLE ALWAYS TRIGGER some_trig_before;
CREATE TABLE some_t1 (c INT, LIKE some_t INCLUDING TRIGGERS INCLUDING COMMENTS);
\d+ some_t1
DROP TABLE some_t;
--
2.34.1
[text/x-patch] v6-0001-CREATE-TABLE-LIKE-INCLUDING-TRIGGERS.patch (41.8K, 3-v6-0001-CREATE-TABLE-LIKE-INCLUDING-TRIGGERS.patch)
download | inline diff:
From 8258e0b167450a6bcf838a2398f64a75edd4e2c5 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Fri, 16 Jan 2026 15:31:33 +0800
Subject: [PATCH v6 1/2] CREATE TABLE LIKE INCLUDING TRIGGERS
This will copy all source table's trigger to the new table. This functionality
also extends to CREATE FOREIGN TABLE LIKE statements.
Internal trigger (such as foreign key associated trigger) won't being copied to
new table. However this command will fail if the source table's trigger contain
whole-row reference.
If INCLUDING COMMENTS is specified, trigger comments will be copied to the new
table.
discussion: https://postgr.es/m/CACJufxHJAr2FjbeB6ghg_-N5dxX5JVnjKSLOUxOyt4TeaAWQkg@mail.gmail.com
commitfest: https://commitfest.postgresql.org/patch/6087
---
doc/src/sgml/ref/create_foreign_table.sgml | 11 +-
doc/src/sgml/ref/create_table.sgml | 13 +-
src/backend/catalog/index.c | 2 +
src/backend/commands/tablecmds.c | 8 +
src/backend/commands/trigger.c | 230 +++++++++++++++++-
src/backend/parser/gram.y | 9 +-
src/backend/parser/parse_utilcmd.c | 37 ++-
src/include/commands/trigger.h | 4 +
src/include/nodes/parsenodes.h | 4 +
src/include/parser/kwlist.h | 1 +
.../regress/expected/create_table_like.out | 10 +
src/test/regress/expected/triggers.out | 130 +++++++++-
src/test/regress/sql/create_table_like.sql | 7 +
src/test/regress/sql/triggers.sql | 67 ++++-
14 files changed, 519 insertions(+), 14 deletions(-)
diff --git a/doc/src/sgml/ref/create_foreign_table.sgml b/doc/src/sgml/ref/create_foreign_table.sgml
index 08a8ceeae75..68d0608b0a3 100644
--- a/doc/src/sgml/ref/create_foreign_table.sgml
+++ b/doc/src/sgml/ref/create_foreign_table.sgml
@@ -60,7 +60,7 @@ CREATE FOREIGN TABLE [ IF NOT EXISTS ] <replaceable class="parameter">table_name
<phrase>and <replaceable class="parameter">like_option</replaceable> is:</phrase>
-{ INCLUDING | EXCLUDING } { COMMENTS | CONSTRAINTS | DEFAULTS | GENERATED | STATISTICS | ALL }
+{ INCLUDING | EXCLUDING } { COMMENTS | CONSTRAINTS | DEFAULTS | GENERATED | STATISTICS | TRIGGERS | ALL }
<phrase>and <replaceable class="parameter">partition_bound_spec</replaceable> is:</phrase>
@@ -284,6 +284,15 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>INCLUDING TRIGGERS</literal></term>
+ <listitem>
+ <para>
+ All non-internal triggers are copied to the new table.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>INCLUDING ALL</literal></term>
<listitem>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 77c5a763d45..c6a0f6dae9b 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -88,7 +88,7 @@ class="parameter">referential_action</replaceable> ] [ ON UPDATE <replaceable cl
<phrase>and <replaceable class="parameter">like_option</replaceable> is:</phrase>
-{ INCLUDING | EXCLUDING } { COMMENTS | COMPRESSION | CONSTRAINTS | DEFAULTS | GENERATED | IDENTITY | INDEXES | STATISTICS | STORAGE | ALL }
+{ INCLUDING | EXCLUDING } { COMMENTS | COMPRESSION | CONSTRAINTS | DEFAULTS | GENERATED | IDENTITY | INDEXES | STATISTICS | STORAGE | TRIGGERS | ALL }
<phrase>and <replaceable class="parameter">partition_bound_spec</replaceable> is:</phrase>
@@ -672,7 +672,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<term><literal>INCLUDING COMMENTS</literal></term>
<listitem>
<para>
- Comments for the copied columns, constraints, and indexes will be
+ Comments for the copied columns, constraints, indexes and triggers will be
copied. The default behavior is to exclude comments, resulting in
the copied columns and constraints in the new table having no
comments.
@@ -776,6 +776,15 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</listitem>
</varlistentry>
+ <varlistentry id="sql-createtable-parms-like-opt-triggers">
+ <term><literal>INCLUDING TRIGGERS</literal></term>
+ <listitem>
+ <para>
+ All non-internal triggers on the original table will be created on the new table.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="sql-createtable-parms-like-opt-all">
<term><literal>INCLUDING ALL</literal></term>
<listitem>
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 43de42ce39e..b0b5a886fa1 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -2037,6 +2037,8 @@ index_constraint_create(Relation heapRelation,
trigger->deferrable = true;
trigger->initdeferred = initdeferred;
trigger->constrrel = NULL;
+ trigger->trigcomment = NULL;
+ trigger->transformed = true;
(void) CreateTrigger(trigger, NULL, RelationGetRelid(heapRelation),
InvalidOid, conOid, indexRelationId, InvalidOid,
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index f976c0e5c7e..6a25f6b7553 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -13864,6 +13864,8 @@ CreateFKCheckTrigger(Oid myRelOid, Oid refRelOid, Constraint *fkconstraint,
fk_trigger->deferrable = fkconstraint->deferrable;
fk_trigger->initdeferred = fkconstraint->initdeferred;
fk_trigger->constrrel = NULL;
+ fk_trigger->trigcomment = NULL;
+ fk_trigger->transformed = true;
trigAddress = CreateTrigger(fk_trigger, NULL, myRelOid, refRelOid,
constraintOid, indexOid, InvalidOid,
@@ -13909,6 +13911,8 @@ createForeignKeyActionTriggers(Oid myRelOid, Oid refRelOid, Constraint *fkconstr
fk_trigger->whenClause = NULL;
fk_trigger->transitionRels = NIL;
fk_trigger->constrrel = NULL;
+ fk_trigger->trigcomment = NULL;
+ fk_trigger->transformed = true;
switch (fkconstraint->fk_del_action)
{
@@ -13969,6 +13973,8 @@ createForeignKeyActionTriggers(Oid myRelOid, Oid refRelOid, Constraint *fkconstr
fk_trigger->whenClause = NULL;
fk_trigger->transitionRels = NIL;
fk_trigger->constrrel = NULL;
+ fk_trigger->trigcomment = NULL;
+ fk_trigger->transformed = true;
switch (fkconstraint->fk_upd_action)
{
@@ -20919,6 +20925,8 @@ CloneRowTriggersToPartition(Relation parent, Relation partition)
trigStmt->deferrable = trigForm->tgdeferrable;
trigStmt->initdeferred = trigForm->tginitdeferred;
trigStmt->constrrel = NULL; /* passed separately */
+ trigStmt->trigcomment = NULL;
+ trigStmt->transformed = true;
CreateTriggerFiringOn(trigStmt, NULL, RelationGetRelid(partition),
trigForm->tgconstrrelid, InvalidOid, InvalidOid,
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
index 8df915f63fb..e944265bd9f 100644
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -30,6 +30,7 @@
#include "catalog/pg_proc.h"
#include "catalog/pg_trigger.h"
#include "catalog/pg_type.h"
+#include "commands/comment.h"
#include "commands/trigger.h"
#include "executor/executor.h"
#include "miscadmin.h"
@@ -589,13 +590,21 @@ CreateTriggerFiringOn(CreateTrigStmt *stmt, const char *queryString,
false, false);
addNSItemToQuery(pstate, nsitem, false, true, true);
- /* Transform expression. Copy to be sure we don't modify original */
- whenClause = transformWhereClause(pstate,
- copyObject(stmt->whenClause),
- EXPR_KIND_TRIGGER_WHEN,
- "WHEN");
- /* we have to fix its collations too */
- assign_expr_collations(pstate, whenClause);
+ if (stmt->transformed)
+ whenClause = stmt->whenClause;
+ else
+ {
+ /* Transform expression. Copy to be sure we don't modify original */
+ whenClause = transformWhereClause(pstate,
+ copyObject(stmt->whenClause),
+ EXPR_KIND_TRIGGER_WHEN,
+ "WHEN");
+
+ /* we have to fix its collations too */
+ assign_expr_collations(pstate, whenClause);
+
+ stmt->transformed = true;
+ }
/*
* Check for disallowed references to OLD/NEW.
@@ -1204,6 +1213,11 @@ CreateTriggerFiringOn(CreateTrigStmt *stmt, const char *queryString,
/* Keep lock on target rel until end of xact */
table_close(rel, NoLock);
+ /* Add any requested comment */
+ if (stmt->trigcomment != NULL)
+ CreateComments(trigoid, TriggerRelationId, 0,
+ stmt->trigcomment);
+
return myself;
}
@@ -6753,3 +6767,205 @@ check_modified_virtual_generated(TupleDesc tupdesc, HeapTuple tuple)
return tuple;
}
+
+/*
+ * Duplicate the trigger definition for the new relation: Use the source trigger
+ * (source_trigid from source_rel) to initialize a CreateTrigStmt for the target
+ * relation (heapRel).
+ *
+ * Attribute numbers in expression Vars are adjusted according to attmap.
+ */
+CreateTrigStmt *
+generateClonedTriggerStmt(RangeVar *heapRel, Oid source_trigid,
+ Relation source_rel, const AttrMap *attmap)
+{
+ HeapTuple triggerTuple;
+ HeapTuple proctup;
+ Form_pg_trigger trigForm;
+ Form_pg_proc procform;
+ Relation pg_trigger;
+ RangeVar *constrrel = NULL;
+ SysScanDesc tgscan;
+ ScanKeyData skey[1];
+ Datum value;
+ bool isnull;
+ Node *qual = NULL;
+ List *trigargs = NIL;
+ List *cols = NIL;
+ List *funcname = NIL;
+ List *transitionRels = NIL;
+ char *funcschema;
+ CreateTrigStmt *trigStmt;
+
+ pg_trigger = table_open(TriggerRelationId, AccessShareLock);
+
+ /* Find the trigger to copy */
+ ScanKeyInit(&skey[0],
+ Anum_pg_trigger_oid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(source_trigid));
+
+ tgscan = systable_beginscan(pg_trigger, TriggerOidIndexId, true,
+ NULL, 1, skey);
+
+ triggerTuple = systable_getnext(tgscan);
+ if (!HeapTupleIsValid(triggerTuple))
+ elog(ERROR, "could not find tuple for trigger %u", source_trigid);
+
+ trigForm = (Form_pg_trigger) GETSTRUCT(triggerTuple);
+
+ /* Reconstruct trigger function String list */
+ proctup = SearchSysCache1(PROCOID, ObjectIdGetDatum(trigForm->tgfoid));
+ if (!HeapTupleIsValid(proctup))
+ elog(ERROR, "cache lookup failed for function %u", trigForm->tgfoid);
+ procform = (Form_pg_proc) GETSTRUCT(proctup);
+
+ funcschema = get_namespace_name(procform->pronamespace);
+ funcname = list_make2(makeString(funcschema),
+ makeString(pstrdup(NameStr(procform->proname))));
+ ReleaseSysCache(proctup);
+
+ /*
+ * If there is a column list, transform it to a list of column names. Note
+ * we don't need to map this list in any way ...
+ */
+ if (trigForm->tgattr.dim1 > 0)
+ {
+ for (int i = 0; i < trigForm->tgattr.dim1; i++)
+ {
+ Form_pg_attribute col;
+
+ col = TupleDescAttr(RelationGetDescr(source_rel),
+ trigForm->tgattr.values[i] - 1);
+ cols = lappend(cols,
+ makeString(pstrdup(NameStr(col->attname))));
+ }
+ }
+
+ /* Reconstruct trigger arguments list */
+ if (trigForm->tgnargs > 0)
+ {
+ bytea *val;
+ char *p;
+
+ val = DatumGetByteaPP(fastgetattr(triggerTuple,
+ Anum_pg_trigger_tgargs,
+ RelationGetDescr(pg_trigger),
+ &isnull));
+ if (isnull)
+ elog(ERROR, "tgargs is null in trigger \"%s\" for relation \"%s\"",
+ NameStr(trigForm->tgname),
+ RelationGetRelationName(source_rel));
+
+ p = (char *) VARDATA_ANY(val);
+
+ for (int i = 0; i < trigForm->tgnargs; i++)
+ {
+ trigargs = lappend(trigargs, makeString(pstrdup(p)));
+ p += strlen(p) + 1;
+ }
+ }
+
+ /* If the trigger has a WHEN qualification, add that */
+ value = fastgetattr(triggerTuple, Anum_pg_trigger_tgqual,
+ RelationGetDescr(pg_trigger), &isnull);
+ if (!isnull)
+ {
+ bool found_whole_row;
+
+ qual = stringToNode(TextDatumGetCString(value));
+
+ /* Adjust Vars to match new table's column numbering */
+ qual = map_variable_attnos(qual, PRS2_NEW_VARNO, 0,
+ attmap,
+ InvalidOid,
+ &found_whole_row);
+
+ /* As in expandTableLikeClause, reject whole-row variables */
+ if (found_whole_row)
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot convert whole-row table reference"),
+ errdetail("Trigger \"%s\" contains a whole-row table reference.",
+ NameStr(trigForm->tgname)));
+
+ qual = map_variable_attnos(qual, PRS2_OLD_VARNO, 0,
+ attmap,
+ InvalidOid,
+ &found_whole_row);
+
+ /* As in expandTableLikeClause, reject whole-row variables */
+ if (found_whole_row)
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot convert whole-row table reference"),
+ errdetail("Trigger \"%s\" contains a whole-row table reference.",
+ NameStr(trigForm->tgname)));
+ }
+
+ /* Reconstruct trigger old transition table */
+ value = fastgetattr(triggerTuple, Anum_pg_trigger_tgoldtable,
+ RelationGetDescr(pg_trigger), &isnull);
+ if (!isnull)
+ {
+ TriggerTransition *old = makeNode(TriggerTransition);
+
+ old->isNew = false;
+ old->name = pstrdup(NameStr(*DatumGetName(value)));
+ old->isTable = true;
+ transitionRels = lappend(transitionRels, old);
+ }
+
+ /* Reconstruct trigger new transition table */
+ value = fastgetattr(triggerTuple, Anum_pg_trigger_tgnewtable,
+ RelationGetDescr(pg_trigger),
+ &isnull);
+ if (!isnull)
+ {
+ TriggerTransition *new = makeNode(TriggerTransition);
+
+ new->isNew = true;
+ new->name = pstrdup(NameStr(*DatumGetName(value)));
+ new->isTable = true;
+ transitionRels = lappend(transitionRels, new);
+ }
+
+ /* Reconstruct trigger constraint's FROM table */
+ if (OidIsValid(trigForm->tgconstrrelid))
+ {
+ /*
+ * Acquire the AccessShareLock lock on tgconstrrelid now, as it will
+ * be required later in CreateTriggerFiringOn.
+ */
+ LockRelationOid(trigForm->tgconstrrelid, AccessShareLock);
+
+ constrrel =
+ makeRangeVar(get_namespace_name(get_rel_namespace(trigForm->tgconstrrelid)),
+ get_rel_name(trigForm->tgconstrrelid),
+ -1);
+ }
+
+ trigStmt = makeNode(CreateTrigStmt);
+ trigStmt->replace = false;
+ trigStmt->isconstraint = OidIsValid(trigForm->tgconstraint);
+ trigStmt->trigname = pstrdup(NameStr(trigForm->tgname));
+ trigStmt->relation = heapRel;
+ trigStmt->funcname = funcname;
+ trigStmt->args = trigargs;
+ trigStmt->row = TRIGGER_FOR_ROW(trigForm->tgtype);
+ trigStmt->timing = trigForm->tgtype & TRIGGER_TYPE_TIMING_MASK;
+ trigStmt->events = trigForm->tgtype & TRIGGER_TYPE_EVENT_MASK;
+ trigStmt->columns = cols;
+ trigStmt->whenClause = qual;
+ trigStmt->transitionRels = transitionRels;
+ trigStmt->deferrable = trigForm->tgdeferrable;
+ trigStmt->initdeferred = trigForm->tginitdeferred;
+ trigStmt->constrrel = constrrel;
+ trigStmt->trigcomment = NULL;
+ trigStmt->transformed = true;
+
+ systable_endscan(tgscan);
+ table_close(pg_trigger, AccessShareLock);
+
+ return trigStmt;
+}
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 713ee5c10a2..ee9cbbfc6c4 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -786,7 +786,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
TABLE TABLES TABLESAMPLE TABLESPACE TARGET TEMP TEMPLATE TEMPORARY TEXT_P THEN
TIES TIME TIMESTAMP TO TRAILING TRANSACTION TRANSFORM
- TREAT TRIGGER TRIM TRUE_P
+ TREAT TRIGGER TRIGGERS TRIM TRUE_P
TRUNCATE TRUSTED TYPE_P TYPES_P
UESCAPE UNBOUNDED UNCONDITIONAL UNCOMMITTED UNENCRYPTED UNION UNIQUE UNKNOWN
@@ -4296,6 +4296,7 @@ TableLikeOption:
| INDEXES { $$ = CREATE_TABLE_LIKE_INDEXES; }
| STATISTICS { $$ = CREATE_TABLE_LIKE_STATISTICS; }
| STORAGE { $$ = CREATE_TABLE_LIKE_STORAGE; }
+ | TRIGGERS { $$ = CREATE_TABLE_LIKE_TRIGGERS; }
| ALL { $$ = CREATE_TABLE_LIKE_ALL; }
;
@@ -6150,6 +6151,8 @@ CreateTrigStmt:
n->deferrable = false;
n->initdeferred = false;
n->constrrel = NULL;
+ n->trigcomment = NULL;
+ n->transformed = false;
$$ = (Node *) n;
}
| CREATE opt_or_replace CONSTRAINT TRIGGER name AFTER TriggerEvents ON
@@ -6200,6 +6203,8 @@ CreateTrigStmt:
&n->deferrable, &n->initdeferred, &dummy,
NULL, NULL, yyscanner);
n->constrrel = $10;
+ n->trigcomment = NULL;
+ n->transformed = false;
$$ = (Node *) n;
}
;
@@ -18194,6 +18199,7 @@ unreserved_keyword:
| TRANSACTION
| TRANSFORM
| TRIGGER
+ | TRIGGERS
| TRUNCATE
| TRUSTED
| TYPE_P
@@ -18847,6 +18853,7 @@ bare_label_keyword:
| TRANSFORM
| TREAT
| TRIGGER
+ | TRIGGERS
| TRIM
| TRUE_P
| TRUNCATE
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index b5f4c72459d..ca446d5e2d6 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -39,12 +39,14 @@
#include "catalog/pg_opclass.h"
#include "catalog/pg_operator.h"
#include "catalog/pg_statistic_ext.h"
+#include "catalog/pg_trigger.h"
#include "catalog/pg_type.h"
#include "commands/comment.h"
#include "commands/defrem.h"
#include "commands/sequence.h"
#include "commands/tablecmds.h"
#include "commands/tablespace.h"
+#include "commands/trigger.h"
#include "miscadmin.h"
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
@@ -1321,7 +1323,8 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
CREATE_TABLE_LIKE_GENERATED |
CREATE_TABLE_LIKE_CONSTRAINTS |
CREATE_TABLE_LIKE_INDEXES |
- CREATE_TABLE_LIKE_STATISTICS))
+ CREATE_TABLE_LIKE_STATISTICS |
+ CREATE_TABLE_LIKE_TRIGGERS))
{
table_like_clause->relationOid = RelationGetRelid(relation);
cxt->likeclauses = lappend(cxt->likeclauses, table_like_clause);
@@ -1625,6 +1628,38 @@ expandTableLikeClause(RangeVar *heapRel, TableLikeClause *table_like_clause)
list_free(parent_extstats);
}
+ /* Process triggers if required */
+ if ((table_like_clause->options & CREATE_TABLE_LIKE_TRIGGERS) &&
+ relation->trigdesc != NULL)
+ {
+ bool include_comments = (table_like_clause->options & CREATE_TABLE_LIKE_COMMENTS);
+
+ for (int nt = 0; nt < relation->trigdesc->numtriggers; nt++)
+ {
+ Trigger *trig = relation->trigdesc->triggers + nt;
+ Oid trigoid = trig->tgoid;
+ CreateTrigStmt *trig_stmt;
+
+ /* We do not copy internal trigger to the new table */
+ if (trig->tgisinternal)
+ continue;
+
+ trig_stmt = generateClonedTriggerStmt(heapRel, trigoid,
+ relation, attmap);
+
+ /* Copy comment on trigger, if requested */
+ if (include_comments)
+ {
+ comment = GetComment(trigoid, TriggerRelationId, 0);
+
+ /* We make use of CreateTrigStmt's trigcomment option */
+ trig_stmt->trigcomment = comment;
+ }
+
+ result = lappend(result, trig_stmt);
+ }
+ }
+
/* Done with child rel */
table_close(childrel, NoLock);
diff --git a/src/include/commands/trigger.h b/src/include/commands/trigger.h
index 556c86bf5e1..870bc206822 100644
--- a/src/include/commands/trigger.h
+++ b/src/include/commands/trigger.h
@@ -268,6 +268,10 @@ extern void AfterTriggerBeginSubXact(void);
extern void AfterTriggerEndSubXact(bool isCommit);
extern void AfterTriggerSetState(ConstraintsSetStmt *stmt);
extern bool AfterTriggerPendingOnRel(Oid relid);
+extern CreateTrigStmt *generateClonedTriggerStmt(RangeVar *heapRel,
+ Oid source_trigid,
+ Relation source_rel,
+ const AttrMap *attmap);
/*
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 646d6ced763..5c3d57982de 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -795,6 +795,7 @@ typedef enum TableLikeOption
CREATE_TABLE_LIKE_INDEXES = 1 << 6,
CREATE_TABLE_LIKE_STATISTICS = 1 << 7,
CREATE_TABLE_LIKE_STORAGE = 1 << 8,
+ CREATE_TABLE_LIKE_TRIGGERS = 1 << 9,
CREATE_TABLE_LIKE_ALL = PG_INT32_MAX
} TableLikeOption;
@@ -3154,6 +3155,9 @@ typedef struct CreateTrigStmt
bool deferrable; /* [NOT] DEFERRABLE */
bool initdeferred; /* INITIALLY {DEFERRED|IMMEDIATE} */
RangeVar *constrrel; /* opposite relation, if RI trigger */
+ char *trigcomment; /* comment to apply to trigger, or NULL */
+ bool transformed; /* true means no need do parse analysis for *
+ * whenClause */
} CreateTrigStmt;
/* ----------------------
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index f7753c5c8a8..00b8803f5aa 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -464,6 +464,7 @@ PG_KEYWORD("transaction", TRANSACTION, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("transform", TRANSFORM, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("treat", TREAT, COL_NAME_KEYWORD, BARE_LABEL)
PG_KEYWORD("trigger", TRIGGER, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("triggers", TRIGGERS, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("trim", TRIM, COL_NAME_KEYWORD, BARE_LABEL)
PG_KEYWORD("true", TRUE_P, RESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("truncate", TRUNCATE, UNRESERVED_KEYWORD, BARE_LABEL)
diff --git a/src/test/regress/expected/create_table_like.out b/src/test/regress/expected/create_table_like.out
index d3c35c14847..0666556e5e6 100644
--- a/src/test/regress/expected/create_table_like.out
+++ b/src/test/regress/expected/create_table_like.out
@@ -631,6 +631,13 @@ Not-null constraints:
"ctl_table_a_not_null" NOT NULL "a"
"ctl_table_d_not_null" NOT NULL "d"
+-- trigger_func was created in triggers.sql
+CREATE TRIGGER trigtest_before_stmt BEFORE DELETE OR UPDATE ON ctl_table
+FOR EACH ROW WHEN (OLD.a > 0)
+EXECUTE PROCEDURE trigger_func('trigtest_before_stmt');
+CREATE TRIGGER trigtest_after_stmt AFTER UPDATE OF a, b ON ctl_table
+FOR EACH STATEMENT
+EXECUTE PROCEDURE trigger_func('trigtest_before_stmt');
-- Test EXCLUDING ALL
CREATE FOREIGN TABLE ctl_foreign_table1(LIKE ctl_table EXCLUDING ALL) SERVER ctl_s0;
\d+ ctl_foreign_table1
@@ -679,6 +686,9 @@ Statistics objects:
Not-null constraints:
"ctl_table_a_not_null" NOT NULL "a"
"ctl_table_d_not_null" NOT NULL "d"
+Triggers:
+ trigtest_after_stmt AFTER UPDATE OF a, b ON ctl_foreign_table2 FOR EACH STATEMENT EXECUTE FUNCTION trigger_func('trigtest_before_stmt')
+ trigtest_before_stmt BEFORE DELETE OR UPDATE ON ctl_foreign_table2 FOR EACH ROW WHEN (old.a > 0) EXECUTE FUNCTION trigger_func('trigtest_before_stmt')
Server: ctl_s0
-- \d+ does not report the value of attcompression for a foreign table, so
diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out
index 1acdd12d29e..7e5413050be 100644
--- a/src/test/regress/expected/triggers.out
+++ b/src/test/regress/expected/triggers.out
@@ -165,6 +165,7 @@ drop table trigtest;
CREATE TABLE log_table (tstamp timestamp default timeofday()::timestamp);
CREATE TABLE main_table (a int unique, b int);
COPY main_table (a,b) FROM stdin;
+-- This function also used in create_table_like.sql, don't drop it.
CREATE FUNCTION trigger_func() RETURNS trigger LANGUAGE plpgsql AS '
BEGIN
RAISE NOTICE ''trigger_func(%) called: action = %, when = %, level = %'', TG_ARGV[0], TG_OP, TG_WHEN, TG_LEVEL;
@@ -174,6 +175,20 @@ CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON main_table
FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('before_ins_stmt');
CREATE TRIGGER after_ins_stmt_trig AFTER INSERT ON main_table
FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('after_ins_stmt');
+CREATE TRIGGER wholetrig BEFORE UPDATE ON main_table FOR EACH ROW
+WHEN (OLD IS NOT NULL)
+EXECUTE PROCEDURE trigger_func('modified_a');
+CREATE TABLE main_table1(LIKE main_table INCLUDING TRIGGERS); --error, wholerow reference
+ERROR: cannot convert whole-row table reference
+DETAIL: Trigger "wholetrig" contains a whole-row table reference.
+DROP TRIGGER wholetrig ON main_table;
+CREATE TRIGGER wholetrig BEFORE UPDATE ON main_table FOR EACH ROW
+WHEN (NEW IS NOT NULL)
+EXECUTE PROCEDURE trigger_func('modified_a');
+CREATE TABLE main_table1(LIKE main_table INCLUDING TRIGGERS); --error, wholerow reference
+ERROR: cannot convert whole-row table reference
+DETAIL: Trigger "wholetrig" contains a whole-row table reference.
+DROP TRIGGER wholetrig ON main_table;
--
-- if neither 'FOR EACH ROW' nor 'FOR EACH STATEMENT' was specified,
-- CREATE TRIGGER should default to 'FOR EACH STATEMENT'
@@ -394,6 +409,44 @@ NOTICE: trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER,
NOTICE: trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW
NOTICE: trigger_func(after_upd_b_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT
NOTICE: trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT
+--CREATE TABLE LIKE INCLUDING TRIGGERS tests
+COMMENT ON TRIGGER before_ins_stmt_trig ON main_table IS 'trigger before_ins_stmt_trig';
+CREATE TABLE main_table1(c int, LIKE main_table INCLUDING TRIGGERS INCLUDING COMMENTS);
+SELECT pg_get_triggerdef(oid)
+FROM pg_trigger
+WHERE NOT tgisinternal AND tgrelid IN ('main_table'::regclass, 'main_table1'::regclass)
+ORDER BY tgname, tgrelid::regclass::text COLLATE "C";
+ pg_get_triggerdef
+------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE TRIGGER after_ins_stmt_trig AFTER INSERT ON public.main_table FOR EACH STATEMENT EXECUTE FUNCTION trigger_func('after_ins_stmt')
+ CREATE TRIGGER after_ins_stmt_trig AFTER INSERT ON public.main_table1 FOR EACH STATEMENT EXECUTE FUNCTION trigger_func('after_ins_stmt')
+ CREATE TRIGGER after_upd_a_b_row_trig AFTER UPDATE OF a, b ON public.main_table FOR EACH ROW EXECUTE FUNCTION trigger_func('after_upd_a_b_row')
+ CREATE TRIGGER after_upd_a_b_row_trig AFTER UPDATE OF a, b ON public.main_table1 FOR EACH ROW EXECUTE FUNCTION trigger_func('after_upd_a_b_row')
+ CREATE TRIGGER after_upd_b_row_trig AFTER UPDATE OF b ON public.main_table FOR EACH ROW EXECUTE FUNCTION trigger_func('after_upd_b_row')
+ CREATE TRIGGER after_upd_b_row_trig AFTER UPDATE OF b ON public.main_table1 FOR EACH ROW EXECUTE FUNCTION trigger_func('after_upd_b_row')
+ CREATE TRIGGER after_upd_b_stmt_trig AFTER UPDATE OF b ON public.main_table FOR EACH STATEMENT EXECUTE FUNCTION trigger_func('after_upd_b_stmt')
+ CREATE TRIGGER after_upd_b_stmt_trig AFTER UPDATE OF b ON public.main_table1 FOR EACH STATEMENT EXECUTE FUNCTION trigger_func('after_upd_b_stmt')
+ CREATE TRIGGER after_upd_stmt_trig AFTER UPDATE ON public.main_table FOR EACH STATEMENT EXECUTE FUNCTION trigger_func('after_upd_stmt')
+ CREATE TRIGGER after_upd_stmt_trig AFTER UPDATE ON public.main_table1 FOR EACH STATEMENT EXECUTE FUNCTION trigger_func('after_upd_stmt')
+ CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON public.main_table FOR EACH STATEMENT EXECUTE FUNCTION trigger_func('before_ins_stmt')
+ CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON public.main_table1 FOR EACH STATEMENT EXECUTE FUNCTION trigger_func('before_ins_stmt')
+ CREATE TRIGGER before_upd_a_row_trig BEFORE UPDATE OF a ON public.main_table FOR EACH ROW EXECUTE FUNCTION trigger_func('before_upd_a_row')
+ CREATE TRIGGER before_upd_a_row_trig BEFORE UPDATE OF a ON public.main_table1 FOR EACH ROW EXECUTE FUNCTION trigger_func('before_upd_a_row')
+ CREATE TRIGGER before_upd_a_stmt_trig BEFORE UPDATE OF a ON public.main_table FOR EACH STATEMENT EXECUTE FUNCTION trigger_func('before_upd_a_stmt')
+ CREATE TRIGGER before_upd_a_stmt_trig BEFORE UPDATE OF a ON public.main_table1 FOR EACH STATEMENT EXECUTE FUNCTION trigger_func('before_upd_a_stmt')
+(16 rows)
+
+SELECT pc.relname, pd.tgname, obj_description(pd.oid, 'pg_trigger')
+FROM pg_trigger pd JOIN pg_class pc
+ON pc.oid = pd.tgrelid AND pd.tgname = 'before_ins_stmt_trig'
+ORDER BY 1;
+ relname | tgname | obj_description
+-------------+----------------------+------------------------------
+ main_table | before_ins_stmt_trig | trigger before_ins_stmt_trig
+ main_table1 | before_ins_stmt_trig | trigger before_ins_stmt_trig
+(2 rows)
+
+COMMENT ON TRIGGER before_ins_stmt_trig ON main_table IS NULL;
--
-- Test case for bug with BEFORE trigger followed by AFTER trigger with WHEN
--
@@ -422,6 +475,20 @@ NOTICE: dummy_update_func(afterb) called: action = UPDATE, old = (t), new = (f)
UPDATE some_t SET some_col = TRUE;
NOTICE: dummy_update_func(before) called: action = UPDATE, old = (f), new = (t)
NOTICE: dummy_update_func(aftera) called: action = UPDATE, old = (f), new = (t)
+CREATE TABLE some_t1 (c INT, LIKE some_t INCLUDING TRIGGERS INCLUDING COMMENTS);
+\d+ some_t1
+ Table "public.some_t1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+----------+---------+-----------+----------+---------+---------+--------------+-------------
+ c | integer | | | | plain | |
+ some_col | boolean | | not null | | plain | |
+Not-null constraints:
+ "some_t_some_col_not_null" NOT NULL "some_col"
+Triggers:
+ some_trig_aftera AFTER UPDATE ON some_t1 FOR EACH ROW WHEN (NOT old.some_col AND new.some_col) EXECUTE FUNCTION dummy_update_func('aftera')
+ some_trig_afterb AFTER UPDATE ON some_t1 FOR EACH ROW WHEN (NOT new.some_col) EXECUTE FUNCTION dummy_update_func('afterb')
+ some_trig_before BEFORE UPDATE ON some_t1 FOR EACH ROW EXECUTE FUNCTION dummy_update_func('before')
+
DROP TABLE some_t;
-- bogus cases
CREATE TRIGGER error_upd_and_col BEFORE UPDATE OR UPDATE OF a ON main_table
@@ -861,6 +928,9 @@ CREATE TRIGGER instead_of_update_trig INSTEAD OF UPDATE ON main_view
FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_upd');
CREATE TRIGGER instead_of_delete_trig INSTEAD OF DELETE ON main_view
FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_del');
+CREATE TABLE main_view_table(LIKE main_view INCLUDING TRIGGERS); --error
+ERROR: "main_view_table" is a table
+DETAIL: Tables cannot have INSTEAD OF triggers.
-- Valid BEFORE statement VIEW triggers
CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON main_view
FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_ins_stmt');
@@ -1769,9 +1839,21 @@ create trigger my_trigger after update on my_view referencing old table as old_t
for each statement execute procedure my_trigger_function();
ERROR: "my_view" is a view
DETAIL: Triggers on views cannot have transition tables.
-drop function my_trigger_function();
+create trigger my_trigger1 before update of i on my_view
+ for each statement execute procedure my_trigger_function();
+create table my_view_copy(LIKE my_view including triggers);
+\d my_view_copy
+ Table "public.my_view_copy"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ i | integer | | |
+Triggers:
+ my_trigger1 BEFORE UPDATE OF i ON my_view_copy FOR EACH STATEMENT EXECUTE FUNCTION my_trigger_function()
+
drop view my_view;
drop table my_table;
+drop table my_view_copy;
+drop function my_trigger_function();
--
-- Verify cases that are unsupported with partitioned tables
--
@@ -2318,6 +2400,20 @@ create constraint trigger parted_trig_two after insert on parted_constr
deferrable initially deferred enforced
for each row when (bark(new.b) AND new.a % 2 = 1)
execute procedure trigger_notice_ab();
+create table parted_constr_copy (like parted_constr including all);
+select pg_get_triggerdef(oid)
+from pg_trigger
+where not tgisinternal and tgrelid in ('parted_constr_copy'::regclass, 'parted_constr'::regclass)
+order by tgname;
+ pg_get_triggerdef
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE CONSTRAINT TRIGGER parted_trig AFTER INSERT ON public.parted_constr DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE FUNCTION trigger_notice_ab()
+ CREATE CONSTRAINT TRIGGER parted_trig AFTER INSERT ON public.parted_constr_copy DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE FUNCTION trigger_notice_ab()
+ CREATE CONSTRAINT TRIGGER parted_trig_two AFTER INSERT ON public.parted_constr DEFERRABLE INITIALLY DEFERRED FOR EACH ROW WHEN ((bark(new.b) AND ((new.a % 2) = 1))) EXECUTE FUNCTION trigger_notice_ab()
+ CREATE CONSTRAINT TRIGGER parted_trig_two AFTER INSERT ON public.parted_constr_copy DEFERRABLE INITIALLY DEFERRED FOR EACH ROW WHEN ((bark(new.b) AND ((new.a % 2) = 1))) EXECUTE FUNCTION trigger_notice_ab()
+(4 rows)
+
+drop table parted_constr_copy;
-- The immediate constraint is fired immediately; the WHEN clause of the
-- deferred constraint is also called immediately. The deferred constraint
-- is fired at commit time.
@@ -3098,6 +3194,18 @@ create trigger iocdu_tt_parted_insert_trig
create trigger iocdu_tt_parted_update_trig
after update on iocdu_tt_parted referencing old table as old_table new table as new_table
for each statement execute procedure dump_update();
+CREATE TABLE iocdu_tt_parted_copy(LIKE iocdu_tt_parted INCLUDING TRIGGERS);
+\d iocdu_tt_parted_copy
+ Table "public.iocdu_tt_parted_copy"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | not null |
+ b | text | | |
+Triggers:
+ iocdu_tt_parted_insert_trig AFTER INSERT ON iocdu_tt_parted_copy REFERENCING NEW TABLE AS new_table FOR EACH STATEMENT EXECUTE FUNCTION dump_insert()
+ iocdu_tt_parted_update_trig AFTER UPDATE ON iocdu_tt_parted_copy REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table FOR EACH STATEMENT EXECUTE FUNCTION dump_update()
+
+DROP TABLE iocdu_tt_parted_copy;
-- inserts only
insert into iocdu_tt_parted values (1, 'AAA'), (2, 'BBB')
on conflict (a) do
@@ -3580,6 +3688,26 @@ begin
end;
$$;
alter function whoami() owner to regress_fn_owner;
+-- CREATE TABLE LIKE INCLUDING TRIGGERS for constraint trigger
+create schema test_trig;
+create table test_trig.trig_t1 (id integer);
+create table test_trig.trig_t2 (id integer);
+alter function whoami() set schema test_trig;
+create constraint trigger con_trig_test after insert on test_trig.trig_t1
+from test_trig.trig_t2
+deferrable initially deferred
+for each row
+execute function test_trig.whoami();
+create table trig_t1_copy(like test_trig.trig_t1 including triggers);
+select pg_get_triggerdef(oid) from pg_trigger where tgrelid = 'trig_t1_copy'::regclass;
+ pg_get_triggerdef
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE CONSTRAINT TRIGGER con_trig_test AFTER INSERT ON public.trig_t1_copy FROM test_trig.trig_t2 DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION test_trig.whoami()
+(1 row)
+
+alter function test_trig.whoami() set schema public;
+drop table test_trig.trig_t1, test_trig.trig_t2, trig_t1_copy;
+drop schema test_trig;
create table defer_trig (id integer);
grant insert on defer_trig to public;
create constraint trigger whoami after insert on defer_trig
diff --git a/src/test/regress/sql/create_table_like.sql b/src/test/regress/sql/create_table_like.sql
index 93389b57dbf..7457e7d961e 100644
--- a/src/test/regress/sql/create_table_like.sql
+++ b/src/test/regress/sql/create_table_like.sql
@@ -258,6 +258,13 @@ ALTER TABLE ctl_table ADD CONSTRAINT foo CHECK (b = 'text');
ALTER TABLE ctl_table ALTER COLUMN b SET STORAGE MAIN;
\d+ ctl_table
+-- trigger_func was created in triggers.sql
+CREATE TRIGGER trigtest_before_stmt BEFORE DELETE OR UPDATE ON ctl_table
+FOR EACH ROW WHEN (OLD.a > 0)
+EXECUTE PROCEDURE trigger_func('trigtest_before_stmt');
+CREATE TRIGGER trigtest_after_stmt AFTER UPDATE OF a, b ON ctl_table
+FOR EACH STATEMENT
+EXECUTE PROCEDURE trigger_func('trigtest_before_stmt');
-- Test EXCLUDING ALL
CREATE FOREIGN TABLE ctl_foreign_table1(LIKE ctl_table EXCLUDING ALL) SERVER ctl_s0;
diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sql
index cc878455ace..064dc22314c 100644
--- a/src/test/regress/sql/triggers.sql
+++ b/src/test/regress/sql/triggers.sql
@@ -108,6 +108,7 @@ COPY main_table (a,b) FROM stdin;
80 15
\.
+-- This function also used in create_table_like.sql, don't drop it.
CREATE FUNCTION trigger_func() RETURNS trigger LANGUAGE plpgsql AS '
BEGIN
RAISE NOTICE ''trigger_func(%) called: action = %, when = %, level = %'', TG_ARGV[0], TG_OP, TG_WHEN, TG_LEVEL;
@@ -120,6 +121,18 @@ FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('before_ins_stmt');
CREATE TRIGGER after_ins_stmt_trig AFTER INSERT ON main_table
FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('after_ins_stmt');
+CREATE TRIGGER wholetrig BEFORE UPDATE ON main_table FOR EACH ROW
+WHEN (OLD IS NOT NULL)
+EXECUTE PROCEDURE trigger_func('modified_a');
+CREATE TABLE main_table1(LIKE main_table INCLUDING TRIGGERS); --error, wholerow reference
+DROP TRIGGER wholetrig ON main_table;
+
+CREATE TRIGGER wholetrig BEFORE UPDATE ON main_table FOR EACH ROW
+WHEN (NEW IS NOT NULL)
+EXECUTE PROCEDURE trigger_func('modified_a');
+CREATE TABLE main_table1(LIKE main_table INCLUDING TRIGGERS); --error, wholerow reference
+DROP TRIGGER wholetrig ON main_table;
+
--
-- if neither 'FOR EACH ROW' nor 'FOR EACH STATEMENT' was specified,
-- CREATE TRIGGER should default to 'FOR EACH STATEMENT'
@@ -234,6 +247,19 @@ SELECT pg_get_triggerdef(oid) FROM pg_trigger WHERE tgrelid = 'main_table'::regc
UPDATE main_table SET a = 50;
UPDATE main_table SET b = 10;
+--CREATE TABLE LIKE INCLUDING TRIGGERS tests
+COMMENT ON TRIGGER before_ins_stmt_trig ON main_table IS 'trigger before_ins_stmt_trig';
+CREATE TABLE main_table1(c int, LIKE main_table INCLUDING TRIGGERS INCLUDING COMMENTS);
+SELECT pg_get_triggerdef(oid)
+FROM pg_trigger
+WHERE NOT tgisinternal AND tgrelid IN ('main_table'::regclass, 'main_table1'::regclass)
+ORDER BY tgname, tgrelid::regclass::text COLLATE "C";
+SELECT pc.relname, pd.tgname, obj_description(pd.oid, 'pg_trigger')
+FROM pg_trigger pd JOIN pg_class pc
+ON pc.oid = pd.tgrelid AND pd.tgname = 'before_ins_stmt_trig'
+ORDER BY 1;
+COMMENT ON TRIGGER before_ins_stmt_trig ON main_table IS NULL;
+
--
-- Test case for bug with BEFORE trigger followed by AFTER trigger with WHEN
--
@@ -258,6 +284,8 @@ INSERT INTO some_t VALUES (TRUE);
UPDATE some_t SET some_col = TRUE;
UPDATE some_t SET some_col = FALSE;
UPDATE some_t SET some_col = TRUE;
+CREATE TABLE some_t1 (c INT, LIKE some_t INCLUDING TRIGGERS INCLUDING COMMENTS);
+\d+ some_t1
DROP TABLE some_t;
-- bogus cases
@@ -621,6 +649,8 @@ FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_upd');
CREATE TRIGGER instead_of_delete_trig INSTEAD OF DELETE ON main_view
FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_del');
+CREATE TABLE main_view_table(LIKE main_view INCLUDING TRIGGERS); --error
+
-- Valid BEFORE statement VIEW triggers
CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON main_view
FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_ins_stmt');
@@ -1214,9 +1244,14 @@ create view my_view as select * from my_table;
create function my_trigger_function() returns trigger as $$ begin end; $$ language plpgsql;
create trigger my_trigger after update on my_view referencing old table as old_table
for each statement execute procedure my_trigger_function();
-drop function my_trigger_function();
+create trigger my_trigger1 before update of i on my_view
+ for each statement execute procedure my_trigger_function();
+create table my_view_copy(LIKE my_view including triggers);
+\d my_view_copy
drop view my_view;
drop table my_table;
+drop table my_view_copy;
+drop function my_trigger_function();
--
-- Verify cases that are unsupported with partitioned tables
@@ -1608,6 +1643,13 @@ create constraint trigger parted_trig_two after insert on parted_constr
for each row when (bark(new.b) AND new.a % 2 = 1)
execute procedure trigger_notice_ab();
+create table parted_constr_copy (like parted_constr including all);
+select pg_get_triggerdef(oid)
+from pg_trigger
+where not tgisinternal and tgrelid in ('parted_constr_copy'::regclass, 'parted_constr'::regclass)
+order by tgname;
+drop table parted_constr_copy;
+
-- The immediate constraint is fired immediately; the WHEN clause of the
-- deferred constraint is also called immediately. The deferred constraint
-- is fired at commit time.
@@ -2282,6 +2324,10 @@ create trigger iocdu_tt_parted_update_trig
after update on iocdu_tt_parted referencing old table as old_table new table as new_table
for each statement execute procedure dump_update();
+CREATE TABLE iocdu_tt_parted_copy(LIKE iocdu_tt_parted INCLUDING TRIGGERS);
+\d iocdu_tt_parted_copy
+DROP TABLE iocdu_tt_parted_copy;
+
-- inserts only
insert into iocdu_tt_parted values (1, 'AAA'), (2, 'BBB')
on conflict (a) do
@@ -2739,6 +2785,25 @@ end;
$$;
alter function whoami() owner to regress_fn_owner;
+-- CREATE TABLE LIKE INCLUDING TRIGGERS for constraint trigger
+create schema test_trig;
+create table test_trig.trig_t1 (id integer);
+create table test_trig.trig_t2 (id integer);
+alter function whoami() set schema test_trig;
+
+create constraint trigger con_trig_test after insert on test_trig.trig_t1
+from test_trig.trig_t2
+deferrable initially deferred
+for each row
+execute function test_trig.whoami();
+
+create table trig_t1_copy(like test_trig.trig_t1 including triggers);
+select pg_get_triggerdef(oid) from pg_trigger where tgrelid = 'trig_t1_copy'::regclass;
+
+alter function test_trig.whoami() set schema public;
+drop table test_trig.trig_t1, test_trig.trig_t2, trig_t1_copy;
+drop schema test_trig;
+
create table defer_trig (id integer);
grant insert on defer_trig to public;
create constraint trigger whoami after insert on defer_trig
--
2.34.1
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: CREATE TABLE LIKE INCLUDING TRIGGERS
2026-01-16 07:34 Re: CREATE TABLE LIKE INCLUDING TRIGGERS jian he <[email protected]>
2026-01-21 12:13 ` Re: CREATE TABLE LIKE INCLUDING TRIGGERS Zsolt Parragi <[email protected]>
2026-01-22 06:08 ` Re: CREATE TABLE LIKE INCLUDING TRIGGERS jian he <[email protected]>
2026-01-22 14:20 ` Re: CREATE TABLE LIKE INCLUDING TRIGGERS Zsolt Parragi <[email protected]>
2026-02-05 06:15 ` Re: CREATE TABLE LIKE INCLUDING TRIGGERS jian he <[email protected]>
@ 2026-02-06 14:49 ` Zsolt Parragi <[email protected]>
2026-02-09 07:43 ` Re: CREATE TABLE LIKE INCLUDING TRIGGERS jian he <[email protected]>
0 siblings, 1 reply; 12+ messages in thread
From: Zsolt Parragi @ 2026-02-06 14:49 UTC (permalink / raw)
To: jian he <[email protected]>; +Cc: Andrey Borodin <[email protected]>; pgsql-hackers
This looks good to me, I only have two minor comments:
1. The test should drop main_table1 and some_t1
2. there's a missing word in a comment (do -> to do)
+ char *trigcomment; /* comment to apply to trigger, or NULL */
+ bool transformed; /* true means no need do parse analysis for *
+ * whenClause */
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: CREATE TABLE LIKE INCLUDING TRIGGERS
2026-01-16 07:34 Re: CREATE TABLE LIKE INCLUDING TRIGGERS jian he <[email protected]>
2026-01-21 12:13 ` Re: CREATE TABLE LIKE INCLUDING TRIGGERS Zsolt Parragi <[email protected]>
2026-01-22 06:08 ` Re: CREATE TABLE LIKE INCLUDING TRIGGERS jian he <[email protected]>
2026-01-22 14:20 ` Re: CREATE TABLE LIKE INCLUDING TRIGGERS Zsolt Parragi <[email protected]>
2026-02-05 06:15 ` Re: CREATE TABLE LIKE INCLUDING TRIGGERS jian he <[email protected]>
2026-02-06 14:49 ` Re: CREATE TABLE LIKE INCLUDING TRIGGERS Zsolt Parragi <[email protected]>
@ 2026-02-09 07:43 ` jian he <[email protected]>
2026-05-27 06:03 ` Re: CREATE TABLE LIKE INCLUDING TRIGGERS lakshmi <[email protected]>
2026-05-27 22:53 ` Re: CREATE TABLE LIKE INCLUDING TRIGGERS Zsolt Parragi <[email protected]>
2026-06-04 02:10 ` Re: CREATE TABLE LIKE INCLUDING TRIGGERS jian he <[email protected]>
0 siblings, 3 replies; 12+ messages in thread
From: jian he @ 2026-02-09 07:43 UTC (permalink / raw)
To: Zsolt Parragi <[email protected]>; +Cc: Andrey Borodin <[email protected]>; pgsql-hackers
On Fri, Feb 6, 2026 at 10:49 PM Zsolt Parragi <[email protected]> wrote:
>
> This looks good to me, I only have two minor comments:
>
> 1. The test should drop main_table1 and some_t1
>
> 2. there's a missing word in a comment (do -> to do)
>
> + char *trigcomment; /* comment to apply to trigger, or NULL */
> + bool transformed; /* true means no need do parse analysis for *
> + * whenClause */
hi.
The attached have addressed these two points.
I still keep two separate patches, in case there are differing opinions about
whether to copy the old table’s trigger tgenabled to the new table.
Squashing them into a single patch is quite straightforward.
--
jian
https://www.enterprisedb.com/
Attachments:
[text/x-patch] v7-0002-CREATE-TABLE-LIKE-INCLUDING-TRIGGERS-copies-tgenabled.patch (7.8K, 2-v7-0002-CREATE-TABLE-LIKE-INCLUDING-TRIGGERS-copies-tgenabled.patch)
download | inline diff:
From 3426508a69882eaeff51a5a363b277ff5174630f Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Mon, 9 Feb 2026 15:36:35 +0800
Subject: [PATCH v7 2/2] CREATE TABLE LIKE INCLUDING TRIGGERS copies tgenabled
discussion: https://postgr.es/m/CACJufxHJAr2FjbeB6ghg_-N5dxX5JVnjKSLOUxOyt4TeaAWQkg@mail.gmail.com
commitfest: https://commitfest.postgresql.org/patch/6087
---
src/backend/catalog/index.c | 1 +
src/backend/commands/tablecmds.c | 4 ++++
src/backend/commands/trigger.c | 1 +
src/backend/parser/gram.y | 2 ++
src/backend/tcop/utility.c | 12 ++++++++----
src/include/nodes/parsenodes.h | 1 +
src/test/regress/expected/triggers.out | 9 +++++++--
src/test/regress/sql/triggers.sql | 3 +++
8 files changed, 27 insertions(+), 6 deletions(-)
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index b0b5a886fa1..d4570626e0b 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -2021,6 +2021,7 @@ index_constraint_create(Relation heapRelation,
CreateTrigStmt *trigger = makeNode(CreateTrigStmt);
trigger->replace = false;
+ trigger->tgenabled = TRIGGER_FIRES_ON_ORIGIN;
trigger->isconstraint = true;
trigger->trigname = (constraintType == CONSTRAINT_PRIMARY) ?
"PK_ConstraintTrigger" :
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 6a25f6b7553..6c53df1e54a 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -13839,6 +13839,7 @@ CreateFKCheckTrigger(Oid myRelOid, Oid refRelOid, Constraint *fkconstraint,
*/
fk_trigger = makeNode(CreateTrigStmt);
fk_trigger->replace = false;
+ fk_trigger->tgenabled = TRIGGER_FIRES_ON_ORIGIN;
fk_trigger->isconstraint = true;
fk_trigger->trigname = "RI_ConstraintTrigger_c";
fk_trigger->relation = NULL;
@@ -13900,6 +13901,7 @@ createForeignKeyActionTriggers(Oid myRelOid, Oid refRelOid, Constraint *fkconstr
*/
fk_trigger = makeNode(CreateTrigStmt);
fk_trigger->replace = false;
+ fk_trigger->tgenabled = TRIGGER_FIRES_ON_ORIGIN;
fk_trigger->isconstraint = true;
fk_trigger->trigname = "RI_ConstraintTrigger_a";
fk_trigger->relation = NULL;
@@ -13962,6 +13964,7 @@ createForeignKeyActionTriggers(Oid myRelOid, Oid refRelOid, Constraint *fkconstr
*/
fk_trigger = makeNode(CreateTrigStmt);
fk_trigger->replace = false;
+ fk_trigger->tgenabled = TRIGGER_FIRES_ON_ORIGIN;
fk_trigger->isconstraint = true;
fk_trigger->trigname = "RI_ConstraintTrigger_a";
fk_trigger->relation = NULL;
@@ -20911,6 +20914,7 @@ CloneRowTriggersToPartition(Relation parent, Relation partition)
trigStmt = makeNode(CreateTrigStmt);
trigStmt->replace = false;
+ trigStmt->tgenabled = trigForm->tgenabled;
trigStmt->isconstraint = OidIsValid(trigForm->tgconstraint);
trigStmt->trigname = NameStr(trigForm->tgname);
trigStmt->relation = NULL;
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
index e944265bd9f..280427559d4 100644
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -6947,6 +6947,7 @@ generateClonedTriggerStmt(RangeVar *heapRel, Oid source_trigid,
trigStmt = makeNode(CreateTrigStmt);
trigStmt->replace = false;
+ trigStmt->tgenabled = trigForm->tgenabled;
trigStmt->isconstraint = OidIsValid(trigForm->tgconstraint);
trigStmt->trigname = pstrdup(NameStr(trigForm->tgname));
trigStmt->relation = heapRel;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index ee9cbbfc6c4..24b27d2c47d 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -6137,6 +6137,7 @@ CreateTrigStmt:
CreateTrigStmt *n = makeNode(CreateTrigStmt);
n->replace = $2;
+ n->tgenabled = TRIGGER_FIRES_ON_ORIGIN;
n->isconstraint = false;
n->trigname = $4;
n->relation = $8;
@@ -6188,6 +6189,7 @@ CreateTrigStmt:
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("CREATE OR REPLACE CONSTRAINT TRIGGER is not supported"),
parser_errposition(@1)));
+ n->tgenabled = TRIGGER_FIRES_ON_ORIGIN;
n->isconstraint = true;
n->trigname = $5;
n->relation = $9;
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index 34dd6e18df5..d240ecd4077 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -1701,10 +1701,14 @@ ProcessUtilitySlow(ParseState *pstate,
break;
case T_CreateTrigStmt:
- address = CreateTrigger((CreateTrigStmt *) parsetree,
- queryString, InvalidOid, InvalidOid,
- InvalidOid, InvalidOid, InvalidOid,
- InvalidOid, NULL, false, false);
+ {
+ CreateTrigStmt *stmt = (CreateTrigStmt *) parsetree;
+
+ address = CreateTriggerFiringOn(stmt, queryString, InvalidOid, InvalidOid,
+ InvalidOid, InvalidOid, InvalidOid,
+ InvalidOid, NULL, false,
+ false, stmt->tgenabled);
+ }
break;
case T_CreatePLangStmt:
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 47e6dfb2ab2..e98cc8c289b 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -3137,6 +3137,7 @@ typedef struct CreateTrigStmt
{
NodeTag type;
bool replace; /* replace trigger if already exists */
+ char tgenabled; /* trigger's firing configuration */
bool isconstraint; /* This is a constraint trigger */
char *trigname; /* TRIGGER's name */
RangeVar *relation; /* relation trigger is on */
diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out
index e8eef2022f3..9f405de807d 100644
--- a/src/test/regress/expected/triggers.out
+++ b/src/test/regress/expected/triggers.out
@@ -476,6 +476,9 @@ NOTICE: dummy_update_func(afterb) called: action = UPDATE, old = (t), new = (f)
UPDATE some_t SET some_col = TRUE;
NOTICE: dummy_update_func(before) called: action = UPDATE, old = (f), new = (t)
NOTICE: dummy_update_func(aftera) called: action = UPDATE, old = (f), new = (t)
+ALTER TABLE some_t ENABLE REPLICA TRIGGER some_trig_aftera;
+ALTER TABLE some_t DISABLE TRIGGER some_trig_afterb;
+ALTER TABLE some_t ENABLE ALWAYS TRIGGER some_trig_before;
CREATE TABLE some_t1 (c INT, LIKE some_t INCLUDING TRIGGERS INCLUDING COMMENTS);
\d+ some_t1
Table "public.some_t1"
@@ -485,10 +488,12 @@ CREATE TABLE some_t1 (c INT, LIKE some_t INCLUDING TRIGGERS INCLUDING COMMENTS);
some_col | boolean | | not null | | plain | |
Not-null constraints:
"some_t_some_col_not_null" NOT NULL "some_col"
-Triggers:
- some_trig_aftera AFTER UPDATE ON some_t1 FOR EACH ROW WHEN (NOT old.some_col AND new.some_col) EXECUTE FUNCTION dummy_update_func('aftera')
+Disabled user triggers:
some_trig_afterb AFTER UPDATE ON some_t1 FOR EACH ROW WHEN (NOT new.some_col) EXECUTE FUNCTION dummy_update_func('afterb')
+Triggers firing always:
some_trig_before BEFORE UPDATE ON some_t1 FOR EACH ROW EXECUTE FUNCTION dummy_update_func('before')
+Triggers firing on replica only:
+ some_trig_aftera AFTER UPDATE ON some_t1 FOR EACH ROW WHEN (NOT old.some_col AND new.some_col) EXECUTE FUNCTION dummy_update_func('aftera')
DROP TABLE some_t;
DROP TABLE some_t1;
diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sql
index 19ef0353cb8..154d9b22aca 100644
--- a/src/test/regress/sql/triggers.sql
+++ b/src/test/regress/sql/triggers.sql
@@ -286,6 +286,9 @@ INSERT INTO some_t VALUES (TRUE);
UPDATE some_t SET some_col = TRUE;
UPDATE some_t SET some_col = FALSE;
UPDATE some_t SET some_col = TRUE;
+ALTER TABLE some_t ENABLE REPLICA TRIGGER some_trig_aftera;
+ALTER TABLE some_t DISABLE TRIGGER some_trig_afterb;
+ALTER TABLE some_t ENABLE ALWAYS TRIGGER some_trig_before;
CREATE TABLE some_t1 (c INT, LIKE some_t INCLUDING TRIGGERS INCLUDING COMMENTS);
\d+ some_t1
DROP TABLE some_t;
--
2.34.1
[text/x-patch] v7-0001-CREATE-TABLE-LIKE-INCLUDING-TRIGGERS.patch (42.3K, 3-v7-0001-CREATE-TABLE-LIKE-INCLUDING-TRIGGERS.patch)
download | inline diff:
From 0197b1f5934776e6cbca62e517d4f5d7f5169048 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Mon, 9 Feb 2026 15:33:13 +0800
Subject: [PATCH v7 1/2] CREATE TABLE LIKE INCLUDING TRIGGERS
This will copy all source table's trigger to the new table. This functionality
also extends to CREATE FOREIGN TABLE LIKE statements.
Internal trigger (such as foreign key associated trigger) won't being copied to
new table. However this command will fail if the source table's trigger contain
whole-row reference.
If INCLUDING COMMENTS is specified, trigger comments will be copied to the new
table.
Author: jian he <[email protected]>
Reviewed-by: Andrey Borodin <[email protected]>
Reviewed-by: Zsolt Parragi <[email protected]>
Reviewed-by: Robert Haas <[email protected]>
discussion: https://postgr.es/m/CACJufxHJAr2FjbeB6ghg_-N5dxX5JVnjKSLOUxOyt4TeaAWQkg@mail.gmail.com
commitfest: https://commitfest.postgresql.org/patch/6087
---
doc/src/sgml/ref/create_foreign_table.sgml | 11 +-
doc/src/sgml/ref/create_table.sgml | 13 +-
src/backend/catalog/index.c | 2 +
src/backend/commands/tablecmds.c | 8 +
src/backend/commands/trigger.c | 230 +++++++++++++++++-
src/backend/parser/gram.y | 9 +-
src/backend/parser/parse_utilcmd.c | 37 ++-
src/include/commands/trigger.h | 4 +
src/include/nodes/parsenodes.h | 4 +
src/include/parser/kwlist.h | 1 +
.../regress/expected/create_table_like.out | 10 +
src/test/regress/expected/triggers.out | 132 +++++++++-
src/test/regress/sql/create_table_like.sql | 7 +
src/test/regress/sql/triggers.sql | 70 +++++-
14 files changed, 524 insertions(+), 14 deletions(-)
diff --git a/doc/src/sgml/ref/create_foreign_table.sgml b/doc/src/sgml/ref/create_foreign_table.sgml
index 08a8ceeae75..68d0608b0a3 100644
--- a/doc/src/sgml/ref/create_foreign_table.sgml
+++ b/doc/src/sgml/ref/create_foreign_table.sgml
@@ -60,7 +60,7 @@ CREATE FOREIGN TABLE [ IF NOT EXISTS ] <replaceable class="parameter">table_name
<phrase>and <replaceable class="parameter">like_option</replaceable> is:</phrase>
-{ INCLUDING | EXCLUDING } { COMMENTS | CONSTRAINTS | DEFAULTS | GENERATED | STATISTICS | ALL }
+{ INCLUDING | EXCLUDING } { COMMENTS | CONSTRAINTS | DEFAULTS | GENERATED | STATISTICS | TRIGGERS | ALL }
<phrase>and <replaceable class="parameter">partition_bound_spec</replaceable> is:</phrase>
@@ -284,6 +284,15 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>INCLUDING TRIGGERS</literal></term>
+ <listitem>
+ <para>
+ All non-internal triggers are copied to the new table.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>INCLUDING ALL</literal></term>
<listitem>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 77c5a763d45..c6a0f6dae9b 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -88,7 +88,7 @@ class="parameter">referential_action</replaceable> ] [ ON UPDATE <replaceable cl
<phrase>and <replaceable class="parameter">like_option</replaceable> is:</phrase>
-{ INCLUDING | EXCLUDING } { COMMENTS | COMPRESSION | CONSTRAINTS | DEFAULTS | GENERATED | IDENTITY | INDEXES | STATISTICS | STORAGE | ALL }
+{ INCLUDING | EXCLUDING } { COMMENTS | COMPRESSION | CONSTRAINTS | DEFAULTS | GENERATED | IDENTITY | INDEXES | STATISTICS | STORAGE | TRIGGERS | ALL }
<phrase>and <replaceable class="parameter">partition_bound_spec</replaceable> is:</phrase>
@@ -672,7 +672,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<term><literal>INCLUDING COMMENTS</literal></term>
<listitem>
<para>
- Comments for the copied columns, constraints, and indexes will be
+ Comments for the copied columns, constraints, indexes and triggers will be
copied. The default behavior is to exclude comments, resulting in
the copied columns and constraints in the new table having no
comments.
@@ -776,6 +776,15 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</listitem>
</varlistentry>
+ <varlistentry id="sql-createtable-parms-like-opt-triggers">
+ <term><literal>INCLUDING TRIGGERS</literal></term>
+ <listitem>
+ <para>
+ All non-internal triggers on the original table will be created on the new table.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="sql-createtable-parms-like-opt-all">
<term><literal>INCLUDING ALL</literal></term>
<listitem>
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 43de42ce39e..b0b5a886fa1 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -2037,6 +2037,8 @@ index_constraint_create(Relation heapRelation,
trigger->deferrable = true;
trigger->initdeferred = initdeferred;
trigger->constrrel = NULL;
+ trigger->trigcomment = NULL;
+ trigger->transformed = true;
(void) CreateTrigger(trigger, NULL, RelationGetRelid(heapRelation),
InvalidOid, conOid, indexRelationId, InvalidOid,
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index f976c0e5c7e..6a25f6b7553 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -13864,6 +13864,8 @@ CreateFKCheckTrigger(Oid myRelOid, Oid refRelOid, Constraint *fkconstraint,
fk_trigger->deferrable = fkconstraint->deferrable;
fk_trigger->initdeferred = fkconstraint->initdeferred;
fk_trigger->constrrel = NULL;
+ fk_trigger->trigcomment = NULL;
+ fk_trigger->transformed = true;
trigAddress = CreateTrigger(fk_trigger, NULL, myRelOid, refRelOid,
constraintOid, indexOid, InvalidOid,
@@ -13909,6 +13911,8 @@ createForeignKeyActionTriggers(Oid myRelOid, Oid refRelOid, Constraint *fkconstr
fk_trigger->whenClause = NULL;
fk_trigger->transitionRels = NIL;
fk_trigger->constrrel = NULL;
+ fk_trigger->trigcomment = NULL;
+ fk_trigger->transformed = true;
switch (fkconstraint->fk_del_action)
{
@@ -13969,6 +13973,8 @@ createForeignKeyActionTriggers(Oid myRelOid, Oid refRelOid, Constraint *fkconstr
fk_trigger->whenClause = NULL;
fk_trigger->transitionRels = NIL;
fk_trigger->constrrel = NULL;
+ fk_trigger->trigcomment = NULL;
+ fk_trigger->transformed = true;
switch (fkconstraint->fk_upd_action)
{
@@ -20919,6 +20925,8 @@ CloneRowTriggersToPartition(Relation parent, Relation partition)
trigStmt->deferrable = trigForm->tgdeferrable;
trigStmt->initdeferred = trigForm->tginitdeferred;
trigStmt->constrrel = NULL; /* passed separately */
+ trigStmt->trigcomment = NULL;
+ trigStmt->transformed = true;
CreateTriggerFiringOn(trigStmt, NULL, RelationGetRelid(partition),
trigForm->tgconstrrelid, InvalidOid, InvalidOid,
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
index 8df915f63fb..e944265bd9f 100644
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -30,6 +30,7 @@
#include "catalog/pg_proc.h"
#include "catalog/pg_trigger.h"
#include "catalog/pg_type.h"
+#include "commands/comment.h"
#include "commands/trigger.h"
#include "executor/executor.h"
#include "miscadmin.h"
@@ -589,13 +590,21 @@ CreateTriggerFiringOn(CreateTrigStmt *stmt, const char *queryString,
false, false);
addNSItemToQuery(pstate, nsitem, false, true, true);
- /* Transform expression. Copy to be sure we don't modify original */
- whenClause = transformWhereClause(pstate,
- copyObject(stmt->whenClause),
- EXPR_KIND_TRIGGER_WHEN,
- "WHEN");
- /* we have to fix its collations too */
- assign_expr_collations(pstate, whenClause);
+ if (stmt->transformed)
+ whenClause = stmt->whenClause;
+ else
+ {
+ /* Transform expression. Copy to be sure we don't modify original */
+ whenClause = transformWhereClause(pstate,
+ copyObject(stmt->whenClause),
+ EXPR_KIND_TRIGGER_WHEN,
+ "WHEN");
+
+ /* we have to fix its collations too */
+ assign_expr_collations(pstate, whenClause);
+
+ stmt->transformed = true;
+ }
/*
* Check for disallowed references to OLD/NEW.
@@ -1204,6 +1213,11 @@ CreateTriggerFiringOn(CreateTrigStmt *stmt, const char *queryString,
/* Keep lock on target rel until end of xact */
table_close(rel, NoLock);
+ /* Add any requested comment */
+ if (stmt->trigcomment != NULL)
+ CreateComments(trigoid, TriggerRelationId, 0,
+ stmt->trigcomment);
+
return myself;
}
@@ -6753,3 +6767,205 @@ check_modified_virtual_generated(TupleDesc tupdesc, HeapTuple tuple)
return tuple;
}
+
+/*
+ * Duplicate the trigger definition for the new relation: Use the source trigger
+ * (source_trigid from source_rel) to initialize a CreateTrigStmt for the target
+ * relation (heapRel).
+ *
+ * Attribute numbers in expression Vars are adjusted according to attmap.
+ */
+CreateTrigStmt *
+generateClonedTriggerStmt(RangeVar *heapRel, Oid source_trigid,
+ Relation source_rel, const AttrMap *attmap)
+{
+ HeapTuple triggerTuple;
+ HeapTuple proctup;
+ Form_pg_trigger trigForm;
+ Form_pg_proc procform;
+ Relation pg_trigger;
+ RangeVar *constrrel = NULL;
+ SysScanDesc tgscan;
+ ScanKeyData skey[1];
+ Datum value;
+ bool isnull;
+ Node *qual = NULL;
+ List *trigargs = NIL;
+ List *cols = NIL;
+ List *funcname = NIL;
+ List *transitionRels = NIL;
+ char *funcschema;
+ CreateTrigStmt *trigStmt;
+
+ pg_trigger = table_open(TriggerRelationId, AccessShareLock);
+
+ /* Find the trigger to copy */
+ ScanKeyInit(&skey[0],
+ Anum_pg_trigger_oid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(source_trigid));
+
+ tgscan = systable_beginscan(pg_trigger, TriggerOidIndexId, true,
+ NULL, 1, skey);
+
+ triggerTuple = systable_getnext(tgscan);
+ if (!HeapTupleIsValid(triggerTuple))
+ elog(ERROR, "could not find tuple for trigger %u", source_trigid);
+
+ trigForm = (Form_pg_trigger) GETSTRUCT(triggerTuple);
+
+ /* Reconstruct trigger function String list */
+ proctup = SearchSysCache1(PROCOID, ObjectIdGetDatum(trigForm->tgfoid));
+ if (!HeapTupleIsValid(proctup))
+ elog(ERROR, "cache lookup failed for function %u", trigForm->tgfoid);
+ procform = (Form_pg_proc) GETSTRUCT(proctup);
+
+ funcschema = get_namespace_name(procform->pronamespace);
+ funcname = list_make2(makeString(funcschema),
+ makeString(pstrdup(NameStr(procform->proname))));
+ ReleaseSysCache(proctup);
+
+ /*
+ * If there is a column list, transform it to a list of column names. Note
+ * we don't need to map this list in any way ...
+ */
+ if (trigForm->tgattr.dim1 > 0)
+ {
+ for (int i = 0; i < trigForm->tgattr.dim1; i++)
+ {
+ Form_pg_attribute col;
+
+ col = TupleDescAttr(RelationGetDescr(source_rel),
+ trigForm->tgattr.values[i] - 1);
+ cols = lappend(cols,
+ makeString(pstrdup(NameStr(col->attname))));
+ }
+ }
+
+ /* Reconstruct trigger arguments list */
+ if (trigForm->tgnargs > 0)
+ {
+ bytea *val;
+ char *p;
+
+ val = DatumGetByteaPP(fastgetattr(triggerTuple,
+ Anum_pg_trigger_tgargs,
+ RelationGetDescr(pg_trigger),
+ &isnull));
+ if (isnull)
+ elog(ERROR, "tgargs is null in trigger \"%s\" for relation \"%s\"",
+ NameStr(trigForm->tgname),
+ RelationGetRelationName(source_rel));
+
+ p = (char *) VARDATA_ANY(val);
+
+ for (int i = 0; i < trigForm->tgnargs; i++)
+ {
+ trigargs = lappend(trigargs, makeString(pstrdup(p)));
+ p += strlen(p) + 1;
+ }
+ }
+
+ /* If the trigger has a WHEN qualification, add that */
+ value = fastgetattr(triggerTuple, Anum_pg_trigger_tgqual,
+ RelationGetDescr(pg_trigger), &isnull);
+ if (!isnull)
+ {
+ bool found_whole_row;
+
+ qual = stringToNode(TextDatumGetCString(value));
+
+ /* Adjust Vars to match new table's column numbering */
+ qual = map_variable_attnos(qual, PRS2_NEW_VARNO, 0,
+ attmap,
+ InvalidOid,
+ &found_whole_row);
+
+ /* As in expandTableLikeClause, reject whole-row variables */
+ if (found_whole_row)
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot convert whole-row table reference"),
+ errdetail("Trigger \"%s\" contains a whole-row table reference.",
+ NameStr(trigForm->tgname)));
+
+ qual = map_variable_attnos(qual, PRS2_OLD_VARNO, 0,
+ attmap,
+ InvalidOid,
+ &found_whole_row);
+
+ /* As in expandTableLikeClause, reject whole-row variables */
+ if (found_whole_row)
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot convert whole-row table reference"),
+ errdetail("Trigger \"%s\" contains a whole-row table reference.",
+ NameStr(trigForm->tgname)));
+ }
+
+ /* Reconstruct trigger old transition table */
+ value = fastgetattr(triggerTuple, Anum_pg_trigger_tgoldtable,
+ RelationGetDescr(pg_trigger), &isnull);
+ if (!isnull)
+ {
+ TriggerTransition *old = makeNode(TriggerTransition);
+
+ old->isNew = false;
+ old->name = pstrdup(NameStr(*DatumGetName(value)));
+ old->isTable = true;
+ transitionRels = lappend(transitionRels, old);
+ }
+
+ /* Reconstruct trigger new transition table */
+ value = fastgetattr(triggerTuple, Anum_pg_trigger_tgnewtable,
+ RelationGetDescr(pg_trigger),
+ &isnull);
+ if (!isnull)
+ {
+ TriggerTransition *new = makeNode(TriggerTransition);
+
+ new->isNew = true;
+ new->name = pstrdup(NameStr(*DatumGetName(value)));
+ new->isTable = true;
+ transitionRels = lappend(transitionRels, new);
+ }
+
+ /* Reconstruct trigger constraint's FROM table */
+ if (OidIsValid(trigForm->tgconstrrelid))
+ {
+ /*
+ * Acquire the AccessShareLock lock on tgconstrrelid now, as it will
+ * be required later in CreateTriggerFiringOn.
+ */
+ LockRelationOid(trigForm->tgconstrrelid, AccessShareLock);
+
+ constrrel =
+ makeRangeVar(get_namespace_name(get_rel_namespace(trigForm->tgconstrrelid)),
+ get_rel_name(trigForm->tgconstrrelid),
+ -1);
+ }
+
+ trigStmt = makeNode(CreateTrigStmt);
+ trigStmt->replace = false;
+ trigStmt->isconstraint = OidIsValid(trigForm->tgconstraint);
+ trigStmt->trigname = pstrdup(NameStr(trigForm->tgname));
+ trigStmt->relation = heapRel;
+ trigStmt->funcname = funcname;
+ trigStmt->args = trigargs;
+ trigStmt->row = TRIGGER_FOR_ROW(trigForm->tgtype);
+ trigStmt->timing = trigForm->tgtype & TRIGGER_TYPE_TIMING_MASK;
+ trigStmt->events = trigForm->tgtype & TRIGGER_TYPE_EVENT_MASK;
+ trigStmt->columns = cols;
+ trigStmt->whenClause = qual;
+ trigStmt->transitionRels = transitionRels;
+ trigStmt->deferrable = trigForm->tgdeferrable;
+ trigStmt->initdeferred = trigForm->tginitdeferred;
+ trigStmt->constrrel = constrrel;
+ trigStmt->trigcomment = NULL;
+ trigStmt->transformed = true;
+
+ systable_endscan(tgscan);
+ table_close(pg_trigger, AccessShareLock);
+
+ return trigStmt;
+}
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 713ee5c10a2..ee9cbbfc6c4 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -786,7 +786,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
TABLE TABLES TABLESAMPLE TABLESPACE TARGET TEMP TEMPLATE TEMPORARY TEXT_P THEN
TIES TIME TIMESTAMP TO TRAILING TRANSACTION TRANSFORM
- TREAT TRIGGER TRIM TRUE_P
+ TREAT TRIGGER TRIGGERS TRIM TRUE_P
TRUNCATE TRUSTED TYPE_P TYPES_P
UESCAPE UNBOUNDED UNCONDITIONAL UNCOMMITTED UNENCRYPTED UNION UNIQUE UNKNOWN
@@ -4296,6 +4296,7 @@ TableLikeOption:
| INDEXES { $$ = CREATE_TABLE_LIKE_INDEXES; }
| STATISTICS { $$ = CREATE_TABLE_LIKE_STATISTICS; }
| STORAGE { $$ = CREATE_TABLE_LIKE_STORAGE; }
+ | TRIGGERS { $$ = CREATE_TABLE_LIKE_TRIGGERS; }
| ALL { $$ = CREATE_TABLE_LIKE_ALL; }
;
@@ -6150,6 +6151,8 @@ CreateTrigStmt:
n->deferrable = false;
n->initdeferred = false;
n->constrrel = NULL;
+ n->trigcomment = NULL;
+ n->transformed = false;
$$ = (Node *) n;
}
| CREATE opt_or_replace CONSTRAINT TRIGGER name AFTER TriggerEvents ON
@@ -6200,6 +6203,8 @@ CreateTrigStmt:
&n->deferrable, &n->initdeferred, &dummy,
NULL, NULL, yyscanner);
n->constrrel = $10;
+ n->trigcomment = NULL;
+ n->transformed = false;
$$ = (Node *) n;
}
;
@@ -18194,6 +18199,7 @@ unreserved_keyword:
| TRANSACTION
| TRANSFORM
| TRIGGER
+ | TRIGGERS
| TRUNCATE
| TRUSTED
| TYPE_P
@@ -18847,6 +18853,7 @@ bare_label_keyword:
| TRANSFORM
| TREAT
| TRIGGER
+ | TRIGGERS
| TRIM
| TRUE_P
| TRUNCATE
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index b5f4c72459d..ca446d5e2d6 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -39,12 +39,14 @@
#include "catalog/pg_opclass.h"
#include "catalog/pg_operator.h"
#include "catalog/pg_statistic_ext.h"
+#include "catalog/pg_trigger.h"
#include "catalog/pg_type.h"
#include "commands/comment.h"
#include "commands/defrem.h"
#include "commands/sequence.h"
#include "commands/tablecmds.h"
#include "commands/tablespace.h"
+#include "commands/trigger.h"
#include "miscadmin.h"
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
@@ -1321,7 +1323,8 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
CREATE_TABLE_LIKE_GENERATED |
CREATE_TABLE_LIKE_CONSTRAINTS |
CREATE_TABLE_LIKE_INDEXES |
- CREATE_TABLE_LIKE_STATISTICS))
+ CREATE_TABLE_LIKE_STATISTICS |
+ CREATE_TABLE_LIKE_TRIGGERS))
{
table_like_clause->relationOid = RelationGetRelid(relation);
cxt->likeclauses = lappend(cxt->likeclauses, table_like_clause);
@@ -1625,6 +1628,38 @@ expandTableLikeClause(RangeVar *heapRel, TableLikeClause *table_like_clause)
list_free(parent_extstats);
}
+ /* Process triggers if required */
+ if ((table_like_clause->options & CREATE_TABLE_LIKE_TRIGGERS) &&
+ relation->trigdesc != NULL)
+ {
+ bool include_comments = (table_like_clause->options & CREATE_TABLE_LIKE_COMMENTS);
+
+ for (int nt = 0; nt < relation->trigdesc->numtriggers; nt++)
+ {
+ Trigger *trig = relation->trigdesc->triggers + nt;
+ Oid trigoid = trig->tgoid;
+ CreateTrigStmt *trig_stmt;
+
+ /* We do not copy internal trigger to the new table */
+ if (trig->tgisinternal)
+ continue;
+
+ trig_stmt = generateClonedTriggerStmt(heapRel, trigoid,
+ relation, attmap);
+
+ /* Copy comment on trigger, if requested */
+ if (include_comments)
+ {
+ comment = GetComment(trigoid, TriggerRelationId, 0);
+
+ /* We make use of CreateTrigStmt's trigcomment option */
+ trig_stmt->trigcomment = comment;
+ }
+
+ result = lappend(result, trig_stmt);
+ }
+ }
+
/* Done with child rel */
table_close(childrel, NoLock);
diff --git a/src/include/commands/trigger.h b/src/include/commands/trigger.h
index 556c86bf5e1..870bc206822 100644
--- a/src/include/commands/trigger.h
+++ b/src/include/commands/trigger.h
@@ -268,6 +268,10 @@ extern void AfterTriggerBeginSubXact(void);
extern void AfterTriggerEndSubXact(bool isCommit);
extern void AfterTriggerSetState(ConstraintsSetStmt *stmt);
extern bool AfterTriggerPendingOnRel(Oid relid);
+extern CreateTrigStmt *generateClonedTriggerStmt(RangeVar *heapRel,
+ Oid source_trigid,
+ Relation source_rel,
+ const AttrMap *attmap);
/*
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 646d6ced763..47e6dfb2ab2 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -795,6 +795,7 @@ typedef enum TableLikeOption
CREATE_TABLE_LIKE_INDEXES = 1 << 6,
CREATE_TABLE_LIKE_STATISTICS = 1 << 7,
CREATE_TABLE_LIKE_STORAGE = 1 << 8,
+ CREATE_TABLE_LIKE_TRIGGERS = 1 << 9,
CREATE_TABLE_LIKE_ALL = PG_INT32_MAX
} TableLikeOption;
@@ -3154,6 +3155,9 @@ typedef struct CreateTrigStmt
bool deferrable; /* [NOT] DEFERRABLE */
bool initdeferred; /* INITIALLY {DEFERRED|IMMEDIATE} */
RangeVar *constrrel; /* opposite relation, if RI trigger */
+ char *trigcomment; /* comment to apply to trigger, or NULL */
+ bool transformed; /* true means no need to do parse analysis for
+ * whenClause */
} CreateTrigStmt;
/* ----------------------
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index f7753c5c8a8..00b8803f5aa 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -464,6 +464,7 @@ PG_KEYWORD("transaction", TRANSACTION, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("transform", TRANSFORM, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("treat", TREAT, COL_NAME_KEYWORD, BARE_LABEL)
PG_KEYWORD("trigger", TRIGGER, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("triggers", TRIGGERS, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("trim", TRIM, COL_NAME_KEYWORD, BARE_LABEL)
PG_KEYWORD("true", TRUE_P, RESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("truncate", TRUNCATE, UNRESERVED_KEYWORD, BARE_LABEL)
diff --git a/src/test/regress/expected/create_table_like.out b/src/test/regress/expected/create_table_like.out
index d3c35c14847..0666556e5e6 100644
--- a/src/test/regress/expected/create_table_like.out
+++ b/src/test/regress/expected/create_table_like.out
@@ -631,6 +631,13 @@ Not-null constraints:
"ctl_table_a_not_null" NOT NULL "a"
"ctl_table_d_not_null" NOT NULL "d"
+-- trigger_func was created in triggers.sql
+CREATE TRIGGER trigtest_before_stmt BEFORE DELETE OR UPDATE ON ctl_table
+FOR EACH ROW WHEN (OLD.a > 0)
+EXECUTE PROCEDURE trigger_func('trigtest_before_stmt');
+CREATE TRIGGER trigtest_after_stmt AFTER UPDATE OF a, b ON ctl_table
+FOR EACH STATEMENT
+EXECUTE PROCEDURE trigger_func('trigtest_before_stmt');
-- Test EXCLUDING ALL
CREATE FOREIGN TABLE ctl_foreign_table1(LIKE ctl_table EXCLUDING ALL) SERVER ctl_s0;
\d+ ctl_foreign_table1
@@ -679,6 +686,9 @@ Statistics objects:
Not-null constraints:
"ctl_table_a_not_null" NOT NULL "a"
"ctl_table_d_not_null" NOT NULL "d"
+Triggers:
+ trigtest_after_stmt AFTER UPDATE OF a, b ON ctl_foreign_table2 FOR EACH STATEMENT EXECUTE FUNCTION trigger_func('trigtest_before_stmt')
+ trigtest_before_stmt BEFORE DELETE OR UPDATE ON ctl_foreign_table2 FOR EACH ROW WHEN (old.a > 0) EXECUTE FUNCTION trigger_func('trigtest_before_stmt')
Server: ctl_s0
-- \d+ does not report the value of attcompression for a foreign table, so
diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out
index 1acdd12d29e..e8eef2022f3 100644
--- a/src/test/regress/expected/triggers.out
+++ b/src/test/regress/expected/triggers.out
@@ -165,6 +165,7 @@ drop table trigtest;
CREATE TABLE log_table (tstamp timestamp default timeofday()::timestamp);
CREATE TABLE main_table (a int unique, b int);
COPY main_table (a,b) FROM stdin;
+-- This function also used in create_table_like.sql, don't drop it.
CREATE FUNCTION trigger_func() RETURNS trigger LANGUAGE plpgsql AS '
BEGIN
RAISE NOTICE ''trigger_func(%) called: action = %, when = %, level = %'', TG_ARGV[0], TG_OP, TG_WHEN, TG_LEVEL;
@@ -174,6 +175,20 @@ CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON main_table
FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('before_ins_stmt');
CREATE TRIGGER after_ins_stmt_trig AFTER INSERT ON main_table
FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('after_ins_stmt');
+CREATE TRIGGER wholetrig BEFORE UPDATE ON main_table FOR EACH ROW
+WHEN (OLD IS NOT NULL)
+EXECUTE PROCEDURE trigger_func('modified_a');
+CREATE TABLE main_table1(LIKE main_table INCLUDING TRIGGERS); --error, wholerow reference
+ERROR: cannot convert whole-row table reference
+DETAIL: Trigger "wholetrig" contains a whole-row table reference.
+DROP TRIGGER wholetrig ON main_table;
+CREATE TRIGGER wholetrig BEFORE UPDATE ON main_table FOR EACH ROW
+WHEN (NEW IS NOT NULL)
+EXECUTE PROCEDURE trigger_func('modified_a');
+CREATE TABLE main_table1(LIKE main_table INCLUDING TRIGGERS); --error, wholerow reference
+ERROR: cannot convert whole-row table reference
+DETAIL: Trigger "wholetrig" contains a whole-row table reference.
+DROP TRIGGER wholetrig ON main_table;
--
-- if neither 'FOR EACH ROW' nor 'FOR EACH STATEMENT' was specified,
-- CREATE TRIGGER should default to 'FOR EACH STATEMENT'
@@ -394,6 +409,45 @@ NOTICE: trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER,
NOTICE: trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW
NOTICE: trigger_func(after_upd_b_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT
NOTICE: trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT
+--CREATE TABLE LIKE INCLUDING TRIGGERS tests
+COMMENT ON TRIGGER before_ins_stmt_trig ON main_table IS 'trigger before_ins_stmt_trig';
+CREATE TABLE main_table1(c int, LIKE main_table INCLUDING TRIGGERS INCLUDING COMMENTS);
+SELECT pg_get_triggerdef(oid)
+FROM pg_trigger
+WHERE NOT tgisinternal AND tgrelid IN ('main_table'::regclass, 'main_table1'::regclass)
+ORDER BY tgname, tgrelid::regclass::text COLLATE "C";
+ pg_get_triggerdef
+------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE TRIGGER after_ins_stmt_trig AFTER INSERT ON public.main_table FOR EACH STATEMENT EXECUTE FUNCTION trigger_func('after_ins_stmt')
+ CREATE TRIGGER after_ins_stmt_trig AFTER INSERT ON public.main_table1 FOR EACH STATEMENT EXECUTE FUNCTION trigger_func('after_ins_stmt')
+ CREATE TRIGGER after_upd_a_b_row_trig AFTER UPDATE OF a, b ON public.main_table FOR EACH ROW EXECUTE FUNCTION trigger_func('after_upd_a_b_row')
+ CREATE TRIGGER after_upd_a_b_row_trig AFTER UPDATE OF a, b ON public.main_table1 FOR EACH ROW EXECUTE FUNCTION trigger_func('after_upd_a_b_row')
+ CREATE TRIGGER after_upd_b_row_trig AFTER UPDATE OF b ON public.main_table FOR EACH ROW EXECUTE FUNCTION trigger_func('after_upd_b_row')
+ CREATE TRIGGER after_upd_b_row_trig AFTER UPDATE OF b ON public.main_table1 FOR EACH ROW EXECUTE FUNCTION trigger_func('after_upd_b_row')
+ CREATE TRIGGER after_upd_b_stmt_trig AFTER UPDATE OF b ON public.main_table FOR EACH STATEMENT EXECUTE FUNCTION trigger_func('after_upd_b_stmt')
+ CREATE TRIGGER after_upd_b_stmt_trig AFTER UPDATE OF b ON public.main_table1 FOR EACH STATEMENT EXECUTE FUNCTION trigger_func('after_upd_b_stmt')
+ CREATE TRIGGER after_upd_stmt_trig AFTER UPDATE ON public.main_table FOR EACH STATEMENT EXECUTE FUNCTION trigger_func('after_upd_stmt')
+ CREATE TRIGGER after_upd_stmt_trig AFTER UPDATE ON public.main_table1 FOR EACH STATEMENT EXECUTE FUNCTION trigger_func('after_upd_stmt')
+ CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON public.main_table FOR EACH STATEMENT EXECUTE FUNCTION trigger_func('before_ins_stmt')
+ CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON public.main_table1 FOR EACH STATEMENT EXECUTE FUNCTION trigger_func('before_ins_stmt')
+ CREATE TRIGGER before_upd_a_row_trig BEFORE UPDATE OF a ON public.main_table FOR EACH ROW EXECUTE FUNCTION trigger_func('before_upd_a_row')
+ CREATE TRIGGER before_upd_a_row_trig BEFORE UPDATE OF a ON public.main_table1 FOR EACH ROW EXECUTE FUNCTION trigger_func('before_upd_a_row')
+ CREATE TRIGGER before_upd_a_stmt_trig BEFORE UPDATE OF a ON public.main_table FOR EACH STATEMENT EXECUTE FUNCTION trigger_func('before_upd_a_stmt')
+ CREATE TRIGGER before_upd_a_stmt_trig BEFORE UPDATE OF a ON public.main_table1 FOR EACH STATEMENT EXECUTE FUNCTION trigger_func('before_upd_a_stmt')
+(16 rows)
+
+SELECT pc.relname, pd.tgname, obj_description(pd.oid, 'pg_trigger')
+FROM pg_trigger pd JOIN pg_class pc
+ON pc.oid = pd.tgrelid AND pd.tgname = 'before_ins_stmt_trig'
+ORDER BY 1;
+ relname | tgname | obj_description
+-------------+----------------------+------------------------------
+ main_table | before_ins_stmt_trig | trigger before_ins_stmt_trig
+ main_table1 | before_ins_stmt_trig | trigger before_ins_stmt_trig
+(2 rows)
+
+COMMENT ON TRIGGER before_ins_stmt_trig ON main_table IS NULL;
+DROP TABLE main_table1;
--
-- Test case for bug with BEFORE trigger followed by AFTER trigger with WHEN
--
@@ -422,7 +476,22 @@ NOTICE: dummy_update_func(afterb) called: action = UPDATE, old = (t), new = (f)
UPDATE some_t SET some_col = TRUE;
NOTICE: dummy_update_func(before) called: action = UPDATE, old = (f), new = (t)
NOTICE: dummy_update_func(aftera) called: action = UPDATE, old = (f), new = (t)
+CREATE TABLE some_t1 (c INT, LIKE some_t INCLUDING TRIGGERS INCLUDING COMMENTS);
+\d+ some_t1
+ Table "public.some_t1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+----------+---------+-----------+----------+---------+---------+--------------+-------------
+ c | integer | | | | plain | |
+ some_col | boolean | | not null | | plain | |
+Not-null constraints:
+ "some_t_some_col_not_null" NOT NULL "some_col"
+Triggers:
+ some_trig_aftera AFTER UPDATE ON some_t1 FOR EACH ROW WHEN (NOT old.some_col AND new.some_col) EXECUTE FUNCTION dummy_update_func('aftera')
+ some_trig_afterb AFTER UPDATE ON some_t1 FOR EACH ROW WHEN (NOT new.some_col) EXECUTE FUNCTION dummy_update_func('afterb')
+ some_trig_before BEFORE UPDATE ON some_t1 FOR EACH ROW EXECUTE FUNCTION dummy_update_func('before')
+
DROP TABLE some_t;
+DROP TABLE some_t1;
-- bogus cases
CREATE TRIGGER error_upd_and_col BEFORE UPDATE OR UPDATE OF a ON main_table
FOR EACH ROW EXECUTE PROCEDURE trigger_func('error_upd_and_col');
@@ -861,6 +930,9 @@ CREATE TRIGGER instead_of_update_trig INSTEAD OF UPDATE ON main_view
FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_upd');
CREATE TRIGGER instead_of_delete_trig INSTEAD OF DELETE ON main_view
FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_del');
+CREATE TABLE main_view_table(LIKE main_view INCLUDING TRIGGERS); --error
+ERROR: "main_view_table" is a table
+DETAIL: Tables cannot have INSTEAD OF triggers.
-- Valid BEFORE statement VIEW triggers
CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON main_view
FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_ins_stmt');
@@ -1769,9 +1841,21 @@ create trigger my_trigger after update on my_view referencing old table as old_t
for each statement execute procedure my_trigger_function();
ERROR: "my_view" is a view
DETAIL: Triggers on views cannot have transition tables.
-drop function my_trigger_function();
+create trigger my_trigger1 before update of i on my_view
+ for each statement execute procedure my_trigger_function();
+create table my_view_copy(LIKE my_view including triggers);
+\d my_view_copy
+ Table "public.my_view_copy"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ i | integer | | |
+Triggers:
+ my_trigger1 BEFORE UPDATE OF i ON my_view_copy FOR EACH STATEMENT EXECUTE FUNCTION my_trigger_function()
+
drop view my_view;
drop table my_table;
+drop table my_view_copy;
+drop function my_trigger_function();
--
-- Verify cases that are unsupported with partitioned tables
--
@@ -2318,6 +2402,20 @@ create constraint trigger parted_trig_two after insert on parted_constr
deferrable initially deferred enforced
for each row when (bark(new.b) AND new.a % 2 = 1)
execute procedure trigger_notice_ab();
+create table parted_constr_copy (like parted_constr including all);
+select pg_get_triggerdef(oid)
+from pg_trigger
+where not tgisinternal and tgrelid in ('parted_constr_copy'::regclass, 'parted_constr'::regclass)
+order by tgname;
+ pg_get_triggerdef
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE CONSTRAINT TRIGGER parted_trig AFTER INSERT ON public.parted_constr DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE FUNCTION trigger_notice_ab()
+ CREATE CONSTRAINT TRIGGER parted_trig AFTER INSERT ON public.parted_constr_copy DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE FUNCTION trigger_notice_ab()
+ CREATE CONSTRAINT TRIGGER parted_trig_two AFTER INSERT ON public.parted_constr DEFERRABLE INITIALLY DEFERRED FOR EACH ROW WHEN ((bark(new.b) AND ((new.a % 2) = 1))) EXECUTE FUNCTION trigger_notice_ab()
+ CREATE CONSTRAINT TRIGGER parted_trig_two AFTER INSERT ON public.parted_constr_copy DEFERRABLE INITIALLY DEFERRED FOR EACH ROW WHEN ((bark(new.b) AND ((new.a % 2) = 1))) EXECUTE FUNCTION trigger_notice_ab()
+(4 rows)
+
+drop table parted_constr_copy;
-- The immediate constraint is fired immediately; the WHEN clause of the
-- deferred constraint is also called immediately. The deferred constraint
-- is fired at commit time.
@@ -3098,6 +3196,18 @@ create trigger iocdu_tt_parted_insert_trig
create trigger iocdu_tt_parted_update_trig
after update on iocdu_tt_parted referencing old table as old_table new table as new_table
for each statement execute procedure dump_update();
+CREATE TABLE iocdu_tt_parted_copy(LIKE iocdu_tt_parted INCLUDING TRIGGERS);
+\d iocdu_tt_parted_copy
+ Table "public.iocdu_tt_parted_copy"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | not null |
+ b | text | | |
+Triggers:
+ iocdu_tt_parted_insert_trig AFTER INSERT ON iocdu_tt_parted_copy REFERENCING NEW TABLE AS new_table FOR EACH STATEMENT EXECUTE FUNCTION dump_insert()
+ iocdu_tt_parted_update_trig AFTER UPDATE ON iocdu_tt_parted_copy REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table FOR EACH STATEMENT EXECUTE FUNCTION dump_update()
+
+DROP TABLE iocdu_tt_parted_copy;
-- inserts only
insert into iocdu_tt_parted values (1, 'AAA'), (2, 'BBB')
on conflict (a) do
@@ -3580,6 +3690,26 @@ begin
end;
$$;
alter function whoami() owner to regress_fn_owner;
+-- CREATE TABLE LIKE INCLUDING TRIGGERS for constraint trigger
+create schema test_trig;
+create table test_trig.trig_t1 (id integer);
+create table test_trig.trig_t2 (id integer);
+alter function whoami() set schema test_trig;
+create constraint trigger con_trig_test after insert on test_trig.trig_t1
+from test_trig.trig_t2
+deferrable initially deferred
+for each row
+execute function test_trig.whoami();
+create table trig_t1_copy(like test_trig.trig_t1 including triggers);
+select pg_get_triggerdef(oid) from pg_trigger where tgrelid = 'trig_t1_copy'::regclass;
+ pg_get_triggerdef
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE CONSTRAINT TRIGGER con_trig_test AFTER INSERT ON public.trig_t1_copy FROM test_trig.trig_t2 DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION test_trig.whoami()
+(1 row)
+
+alter function test_trig.whoami() set schema public;
+drop table test_trig.trig_t1, test_trig.trig_t2, trig_t1_copy;
+drop schema test_trig;
create table defer_trig (id integer);
grant insert on defer_trig to public;
create constraint trigger whoami after insert on defer_trig
diff --git a/src/test/regress/sql/create_table_like.sql b/src/test/regress/sql/create_table_like.sql
index 93389b57dbf..7457e7d961e 100644
--- a/src/test/regress/sql/create_table_like.sql
+++ b/src/test/regress/sql/create_table_like.sql
@@ -258,6 +258,13 @@ ALTER TABLE ctl_table ADD CONSTRAINT foo CHECK (b = 'text');
ALTER TABLE ctl_table ALTER COLUMN b SET STORAGE MAIN;
\d+ ctl_table
+-- trigger_func was created in triggers.sql
+CREATE TRIGGER trigtest_before_stmt BEFORE DELETE OR UPDATE ON ctl_table
+FOR EACH ROW WHEN (OLD.a > 0)
+EXECUTE PROCEDURE trigger_func('trigtest_before_stmt');
+CREATE TRIGGER trigtest_after_stmt AFTER UPDATE OF a, b ON ctl_table
+FOR EACH STATEMENT
+EXECUTE PROCEDURE trigger_func('trigtest_before_stmt');
-- Test EXCLUDING ALL
CREATE FOREIGN TABLE ctl_foreign_table1(LIKE ctl_table EXCLUDING ALL) SERVER ctl_s0;
diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sql
index cc878455ace..19ef0353cb8 100644
--- a/src/test/regress/sql/triggers.sql
+++ b/src/test/regress/sql/triggers.sql
@@ -108,6 +108,7 @@ COPY main_table (a,b) FROM stdin;
80 15
\.
+-- This function also used in create_table_like.sql, don't drop it.
CREATE FUNCTION trigger_func() RETURNS trigger LANGUAGE plpgsql AS '
BEGIN
RAISE NOTICE ''trigger_func(%) called: action = %, when = %, level = %'', TG_ARGV[0], TG_OP, TG_WHEN, TG_LEVEL;
@@ -120,6 +121,18 @@ FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('before_ins_stmt');
CREATE TRIGGER after_ins_stmt_trig AFTER INSERT ON main_table
FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('after_ins_stmt');
+CREATE TRIGGER wholetrig BEFORE UPDATE ON main_table FOR EACH ROW
+WHEN (OLD IS NOT NULL)
+EXECUTE PROCEDURE trigger_func('modified_a');
+CREATE TABLE main_table1(LIKE main_table INCLUDING TRIGGERS); --error, wholerow reference
+DROP TRIGGER wholetrig ON main_table;
+
+CREATE TRIGGER wholetrig BEFORE UPDATE ON main_table FOR EACH ROW
+WHEN (NEW IS NOT NULL)
+EXECUTE PROCEDURE trigger_func('modified_a');
+CREATE TABLE main_table1(LIKE main_table INCLUDING TRIGGERS); --error, wholerow reference
+DROP TRIGGER wholetrig ON main_table;
+
--
-- if neither 'FOR EACH ROW' nor 'FOR EACH STATEMENT' was specified,
-- CREATE TRIGGER should default to 'FOR EACH STATEMENT'
@@ -234,6 +247,21 @@ SELECT pg_get_triggerdef(oid) FROM pg_trigger WHERE tgrelid = 'main_table'::regc
UPDATE main_table SET a = 50;
UPDATE main_table SET b = 10;
+--CREATE TABLE LIKE INCLUDING TRIGGERS tests
+COMMENT ON TRIGGER before_ins_stmt_trig ON main_table IS 'trigger before_ins_stmt_trig';
+CREATE TABLE main_table1(c int, LIKE main_table INCLUDING TRIGGERS INCLUDING COMMENTS);
+SELECT pg_get_triggerdef(oid)
+FROM pg_trigger
+WHERE NOT tgisinternal AND tgrelid IN ('main_table'::regclass, 'main_table1'::regclass)
+ORDER BY tgname, tgrelid::regclass::text COLLATE "C";
+SELECT pc.relname, pd.tgname, obj_description(pd.oid, 'pg_trigger')
+FROM pg_trigger pd JOIN pg_class pc
+ON pc.oid = pd.tgrelid AND pd.tgname = 'before_ins_stmt_trig'
+ORDER BY 1;
+COMMENT ON TRIGGER before_ins_stmt_trig ON main_table IS NULL;
+
+DROP TABLE main_table1;
+
--
-- Test case for bug with BEFORE trigger followed by AFTER trigger with WHEN
--
@@ -258,7 +286,10 @@ INSERT INTO some_t VALUES (TRUE);
UPDATE some_t SET some_col = TRUE;
UPDATE some_t SET some_col = FALSE;
UPDATE some_t SET some_col = TRUE;
+CREATE TABLE some_t1 (c INT, LIKE some_t INCLUDING TRIGGERS INCLUDING COMMENTS);
+\d+ some_t1
DROP TABLE some_t;
+DROP TABLE some_t1;
-- bogus cases
CREATE TRIGGER error_upd_and_col BEFORE UPDATE OR UPDATE OF a ON main_table
@@ -621,6 +652,8 @@ FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_upd');
CREATE TRIGGER instead_of_delete_trig INSTEAD OF DELETE ON main_view
FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_del');
+CREATE TABLE main_view_table(LIKE main_view INCLUDING TRIGGERS); --error
+
-- Valid BEFORE statement VIEW triggers
CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON main_view
FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_ins_stmt');
@@ -1214,9 +1247,14 @@ create view my_view as select * from my_table;
create function my_trigger_function() returns trigger as $$ begin end; $$ language plpgsql;
create trigger my_trigger after update on my_view referencing old table as old_table
for each statement execute procedure my_trigger_function();
-drop function my_trigger_function();
+create trigger my_trigger1 before update of i on my_view
+ for each statement execute procedure my_trigger_function();
+create table my_view_copy(LIKE my_view including triggers);
+\d my_view_copy
drop view my_view;
drop table my_table;
+drop table my_view_copy;
+drop function my_trigger_function();
--
-- Verify cases that are unsupported with partitioned tables
@@ -1608,6 +1646,13 @@ create constraint trigger parted_trig_two after insert on parted_constr
for each row when (bark(new.b) AND new.a % 2 = 1)
execute procedure trigger_notice_ab();
+create table parted_constr_copy (like parted_constr including all);
+select pg_get_triggerdef(oid)
+from pg_trigger
+where not tgisinternal and tgrelid in ('parted_constr_copy'::regclass, 'parted_constr'::regclass)
+order by tgname;
+drop table parted_constr_copy;
+
-- The immediate constraint is fired immediately; the WHEN clause of the
-- deferred constraint is also called immediately. The deferred constraint
-- is fired at commit time.
@@ -2282,6 +2327,10 @@ create trigger iocdu_tt_parted_update_trig
after update on iocdu_tt_parted referencing old table as old_table new table as new_table
for each statement execute procedure dump_update();
+CREATE TABLE iocdu_tt_parted_copy(LIKE iocdu_tt_parted INCLUDING TRIGGERS);
+\d iocdu_tt_parted_copy
+DROP TABLE iocdu_tt_parted_copy;
+
-- inserts only
insert into iocdu_tt_parted values (1, 'AAA'), (2, 'BBB')
on conflict (a) do
@@ -2739,6 +2788,25 @@ end;
$$;
alter function whoami() owner to regress_fn_owner;
+-- CREATE TABLE LIKE INCLUDING TRIGGERS for constraint trigger
+create schema test_trig;
+create table test_trig.trig_t1 (id integer);
+create table test_trig.trig_t2 (id integer);
+alter function whoami() set schema test_trig;
+
+create constraint trigger con_trig_test after insert on test_trig.trig_t1
+from test_trig.trig_t2
+deferrable initially deferred
+for each row
+execute function test_trig.whoami();
+
+create table trig_t1_copy(like test_trig.trig_t1 including triggers);
+select pg_get_triggerdef(oid) from pg_trigger where tgrelid = 'trig_t1_copy'::regclass;
+
+alter function test_trig.whoami() set schema public;
+drop table test_trig.trig_t1, test_trig.trig_t2, trig_t1_copy;
+drop schema test_trig;
+
create table defer_trig (id integer);
grant insert on defer_trig to public;
create constraint trigger whoami after insert on defer_trig
--
2.34.1
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: CREATE TABLE LIKE INCLUDING TRIGGERS
2026-01-16 07:34 Re: CREATE TABLE LIKE INCLUDING TRIGGERS jian he <[email protected]>
2026-01-21 12:13 ` Re: CREATE TABLE LIKE INCLUDING TRIGGERS Zsolt Parragi <[email protected]>
2026-01-22 06:08 ` Re: CREATE TABLE LIKE INCLUDING TRIGGERS jian he <[email protected]>
2026-01-22 14:20 ` Re: CREATE TABLE LIKE INCLUDING TRIGGERS Zsolt Parragi <[email protected]>
2026-02-05 06:15 ` Re: CREATE TABLE LIKE INCLUDING TRIGGERS jian he <[email protected]>
2026-02-06 14:49 ` Re: CREATE TABLE LIKE INCLUDING TRIGGERS Zsolt Parragi <[email protected]>
2026-02-09 07:43 ` Re: CREATE TABLE LIKE INCLUDING TRIGGERS jian he <[email protected]>
@ 2026-05-27 06:03 ` lakshmi <[email protected]>
2 siblings, 0 replies; 12+ messages in thread
From: lakshmi @ 2026-05-27 06:03 UTC (permalink / raw)
To: jian he <[email protected]>; +Cc: Zsolt Parragi <[email protected]>; Andrey Borodin <[email protected]>; pgsql-hackers
>
> Hi Jian,
>
> I applied the latest v9 patches on current master and tested them locally.
>
> The patches applied cleanly, PostgreSQL built successfully.
>
> I also did some manual testing for INCLUDING TRIGGERS. Trigger definitions
> were copied correctly to the new table, and the trigger enabled/disabled
> state (tgenabled) was also preserved properly after applying the second
> patch.
>
> Everything worked as expected in my testing.
>
> Thanks for working on this feature.
>
> Regards,
> Lakshmi G
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: CREATE TABLE LIKE INCLUDING TRIGGERS
2026-01-16 07:34 Re: CREATE TABLE LIKE INCLUDING TRIGGERS jian he <[email protected]>
2026-01-21 12:13 ` Re: CREATE TABLE LIKE INCLUDING TRIGGERS Zsolt Parragi <[email protected]>
2026-01-22 06:08 ` Re: CREATE TABLE LIKE INCLUDING TRIGGERS jian he <[email protected]>
2026-01-22 14:20 ` Re: CREATE TABLE LIKE INCLUDING TRIGGERS Zsolt Parragi <[email protected]>
2026-02-05 06:15 ` Re: CREATE TABLE LIKE INCLUDING TRIGGERS jian he <[email protected]>
2026-02-06 14:49 ` Re: CREATE TABLE LIKE INCLUDING TRIGGERS Zsolt Parragi <[email protected]>
2026-02-09 07:43 ` Re: CREATE TABLE LIKE INCLUDING TRIGGERS jian he <[email protected]>
@ 2026-05-27 22:53 ` Zsolt Parragi <[email protected]>
2026-06-04 01:38 ` Re: CREATE TABLE LIKE INCLUDING TRIGGERS jian he <[email protected]>
2 siblings, 1 reply; 12+ messages in thread
From: Zsolt Parragi @ 2026-05-27 22:53 UTC (permalink / raw)
To: [email protected]
Hello!
Sorry for the late reply, somehow I missed the previous updates.
I found one more problematic scenario, I think the patch should ignore
INSTEAD OF triggers:
CREATE TABLE base (id int, val text);
CREATE VIEW v_instead AS SELECT id, val FROM base;
CREATE FUNCTION v_instead_ins() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
INSERT INTO base VALUES (NEW.id, NEW.val);
RETURN NEW;
END;
$$;
CREATE TRIGGER v_instead_trg
INSTEAD OF INSERT ON v_instead
FOR EACH ROW EXECUTE FUNCTION v_instead_ins();
-- currently fails
-- ERROR: "t_all" is a table
-- DETAIL: Tables cannot have INSTEAD OF triggers.
CREATE TABLE t_all (LIKE v_instead INCLUDING ALL);
And also, either I don't understand something here, or the diff can be
simplified a bit:
- /* Transform expression. Copy to be sure we don't modify original */
- whenClause = transformWhereClause(pstate,
- copyObject(stmt->whenClause),
- EXPR_KIND_TRIGGER_WHEN,
- "WHEN");
- /* we have to fix its collations too */
- assign_expr_collations(pstate, whenClause);
+ if (stmt->transformed)
+ whenClause = stmt->whenClause;
+ else
+ {
+ /* Transform expression. Copy to be sure we don't modify original */
+ whenClause = transformWhereClause(pstate,
+ copyObject(stmt->whenClause),
+ EXPR_KIND_TRIGGER_WHEN,
+ "WHEN");
+
+ /* we have to fix its collations too */
+ assign_expr_collations(pstate, whenClause);
+
+ stmt->transformed = true;
+ }
Do we need the last assignment in this diff? It sets
stmt->transformed, but we don't actually transform the statement, we
create a copy. The flag also doesn't seem to be used after that.
Everything seems to work fine if I remove this assignment, and we
don't need the const related function signature changes without it.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: CREATE TABLE LIKE INCLUDING TRIGGERS
2026-01-16 07:34 Re: CREATE TABLE LIKE INCLUDING TRIGGERS jian he <[email protected]>
2026-01-21 12:13 ` Re: CREATE TABLE LIKE INCLUDING TRIGGERS Zsolt Parragi <[email protected]>
2026-01-22 06:08 ` Re: CREATE TABLE LIKE INCLUDING TRIGGERS jian he <[email protected]>
2026-01-22 14:20 ` Re: CREATE TABLE LIKE INCLUDING TRIGGERS Zsolt Parragi <[email protected]>
2026-02-05 06:15 ` Re: CREATE TABLE LIKE INCLUDING TRIGGERS jian he <[email protected]>
2026-02-06 14:49 ` Re: CREATE TABLE LIKE INCLUDING TRIGGERS Zsolt Parragi <[email protected]>
2026-02-09 07:43 ` Re: CREATE TABLE LIKE INCLUDING TRIGGERS jian he <[email protected]>
2026-05-27 22:53 ` Re: CREATE TABLE LIKE INCLUDING TRIGGERS Zsolt Parragi <[email protected]>
@ 2026-06-04 01:38 ` jian he <[email protected]>
0 siblings, 0 replies; 12+ messages in thread
From: jian he @ 2026-06-04 01:38 UTC (permalink / raw)
To: Zsolt Parragi <[email protected]>; +Cc: [email protected]
On Thu, May 28, 2026 at 6:53 AM Zsolt Parragi <[email protected]> wrote:
>
> Hello!
>
> Sorry for the late reply, somehow I missed the previous updates.
>
> I found one more problematic scenario, I think the patch should ignore
> INSTEAD OF triggers:
>
> CREATE TABLE base (id int, val text);
> CREATE VIEW v_instead AS SELECT id, val FROM base;
> CREATE FUNCTION v_instead_ins() RETURNS trigger LANGUAGE plpgsql AS $$
> BEGIN
> INSERT INTO base VALUES (NEW.id, NEW.val);
> RETURN NEW;
> END;
> $$;
> CREATE TRIGGER v_instead_trg
> INSTEAD OF INSERT ON v_instead
> FOR EACH ROW EXECUTE FUNCTION v_instead_ins();
> -- currently fails
> -- ERROR: "t_all" is a table
> -- DETAIL: Tables cannot have INSTEAD OF triggers.
> CREATE TABLE t_all (LIKE v_instead INCLUDING ALL);
>
https://www.postgresql.org/docs/current/sql-createtrigger.html
says
[Statement-level triggers on a view are fired only if the action on
the view is handled by a row-level INSTEAD OF trigger.]
And the view's INSTEAD OF trigger cannot be applied to a regular table.
Therefore, it does not make sense to copy triggers from a source view
to a new regular table.
I intended to skip all source view's triggers completely.
While at it, I found that foreign tables do not support constraint
triggers or triggers with transition tables,
CREATE FOREIGN TABLE LIKE will ignore these triggers
>
> - /* Transform expression. Copy to be sure we don't modify original */
> - whenClause = transformWhereClause(pstate,
> - copyObject(stmt->whenClause),
> - EXPR_KIND_TRIGGER_WHEN,
> - "WHEN");
> - /* we have to fix its collations too */
> - assign_expr_collations(pstate, whenClause);
> + if (stmt->transformed)
> + whenClause = stmt->whenClause;
> + else
> + {
> + /* Transform expression. Copy to be sure we don't modify original */
> + whenClause = transformWhereClause(pstate,
> + copyObject(stmt->whenClause),
> + EXPR_KIND_TRIGGER_WHEN,
> + "WHEN");
> +
> + /* we have to fix its collations too */
> + assign_expr_collations(pstate, whenClause);
> +
> + stmt->transformed = true;
> + }
>
>
> Do we need the last assignment in this diff? It sets
> stmt->transformed, but we don't actually transform the statement, we
> create a copy. The flag also doesn't seem to be used after that.
> Everything seems to work fine if I remove this assignment, and we
> don't need the const related function signature changes without it.
>
You are right, we don't actually transform the stmt->whenClause.
--
jian
https://www.enterprisedb.com/
Attachments:
[application/x-patch] v10-0002-CREATE-TABLE-LIKE-INCLUDING-TRIGGERS-copies-tgenabled.patch (7.8K, 2-v10-0002-CREATE-TABLE-LIKE-INCLUDING-TRIGGERS-copies-tgenabled.patch)
download | inline diff:
From 0f08984a215f180423e6c5d3899d3c3b6b65024e Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Mon, 2 Mar 2026 10:35:26 +0800
Subject: [PATCH v10 2/2] CREATE TABLE LIKE INCLUDING TRIGGERS copies tgenabled
discussion: https://postgr.es/m/CACJufxHJAr2FjbeB6ghg_-N5dxX5JVnjKSLOUxOyt4TeaAWQkg@mail.gmail.com
commitfest: https://commitfest.postgresql.org/patch/6087
---
src/backend/catalog/index.c | 1 +
src/backend/commands/tablecmds.c | 4 ++++
src/backend/commands/trigger.c | 1 +
src/backend/parser/gram.y | 2 ++
src/backend/tcop/utility.c | 12 ++++++++----
src/include/nodes/parsenodes.h | 1 +
src/test/regress/expected/triggers.out | 9 +++++++--
src/test/regress/sql/triggers.sql | 3 +++
8 files changed, 27 insertions(+), 6 deletions(-)
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 6aaaf738662..576f4d2d00f 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -2041,6 +2041,7 @@ index_constraint_create(Relation heapRelation,
CreateTrigStmt *trigger = makeNode(CreateTrigStmt);
trigger->replace = false;
+ trigger->tgenabled = TRIGGER_FIRES_ON_ORIGIN;
trigger->isconstraint = true;
trigger->trigname = (constraintType == CONSTRAINT_PRIMARY) ?
"PK_ConstraintTrigger" :
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 09654aba5f0..c74dd98b342 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -14090,6 +14090,7 @@ CreateFKCheckTrigger(Oid myRelOid, Oid refRelOid, Constraint *fkconstraint,
*/
fk_trigger = makeNode(CreateTrigStmt);
fk_trigger->replace = false;
+ fk_trigger->tgenabled = TRIGGER_FIRES_ON_ORIGIN;
fk_trigger->isconstraint = true;
fk_trigger->trigname = "RI_ConstraintTrigger_c";
fk_trigger->relation = NULL;
@@ -14151,6 +14152,7 @@ createForeignKeyActionTriggers(Oid myRelOid, Oid refRelOid, Constraint *fkconstr
*/
fk_trigger = makeNode(CreateTrigStmt);
fk_trigger->replace = false;
+ fk_trigger->tgenabled = TRIGGER_FIRES_ON_ORIGIN;
fk_trigger->isconstraint = true;
fk_trigger->trigname = "RI_ConstraintTrigger_a";
fk_trigger->relation = NULL;
@@ -14213,6 +14215,7 @@ createForeignKeyActionTriggers(Oid myRelOid, Oid refRelOid, Constraint *fkconstr
*/
fk_trigger = makeNode(CreateTrigStmt);
fk_trigger->replace = false;
+ fk_trigger->tgenabled = TRIGGER_FIRES_ON_ORIGIN;
fk_trigger->isconstraint = true;
fk_trigger->trigname = "RI_ConstraintTrigger_a";
fk_trigger->relation = NULL;
@@ -21206,6 +21209,7 @@ CloneRowTriggersToPartition(Relation parent, Relation partition)
trigStmt = makeNode(CreateTrigStmt);
trigStmt->replace = false;
+ trigStmt->tgenabled = trigForm->tgenabled;
trigStmt->isconstraint = OidIsValid(trigForm->tgconstraint);
trigStmt->trigname = NameStr(trigForm->tgname);
trigStmt->relation = NULL;
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
index f55a08f0088..ab085bf1f41 100644
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -7096,6 +7096,7 @@ generateClonedTriggerStmt(RangeVar *heapRel, Oid source_trigid,
trigStmt = makeNode(CreateTrigStmt);
trigStmt->replace = false;
+ trigStmt->tgenabled = trigForm->tgenabled;
trigStmt->isconstraint = OidIsValid(trigForm->tgconstraint);
trigStmt->trigname = pstrdup(NameStr(trigForm->tgname));
trigStmt->relation = heapRel;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 6fca2636116..e0dfce69259 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -6196,6 +6196,7 @@ CreateTrigStmt:
CreateTrigStmt *n = makeNode(CreateTrigStmt);
n->replace = $2;
+ n->tgenabled = TRIGGER_FIRES_ON_ORIGIN;
n->isconstraint = false;
n->trigname = $4;
n->relation = $8;
@@ -6247,6 +6248,7 @@ CreateTrigStmt:
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("CREATE OR REPLACE CONSTRAINT TRIGGER is not supported"),
parser_errposition(@1)));
+ n->tgenabled = TRIGGER_FIRES_ON_ORIGIN;
n->isconstraint = true;
n->trigname = $5;
n->relation = $9;
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index 73a56f1df1d..f708df57ee3 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -1705,10 +1705,14 @@ ProcessUtilitySlow(ParseState *pstate,
break;
case T_CreateTrigStmt:
- address = CreateTrigger((CreateTrigStmt *) parsetree,
- queryString, InvalidOid, InvalidOid,
- InvalidOid, InvalidOid, InvalidOid,
- InvalidOid, NULL, false, false);
+ {
+ CreateTrigStmt *stmt = (CreateTrigStmt *) parsetree;
+
+ address = CreateTriggerFiringOn(stmt, queryString, InvalidOid, InvalidOid,
+ InvalidOid, InvalidOid, InvalidOid,
+ InvalidOid, NULL, false,
+ false, stmt->tgenabled);
+ }
break;
case T_CreatePLangStmt:
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index a7d01f5f9fd..c53d9d6bd3e 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -3217,6 +3217,7 @@ typedef struct CreateTrigStmt
{
NodeTag type;
bool replace; /* replace trigger if already exists */
+ char tgenabled; /* trigger's firing configuration */
bool isconstraint; /* This is a constraint trigger */
char *trigname; /* TRIGGER's name */
RangeVar *relation; /* relation trigger is on */
diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out
index f675786739d..2e9c352a690 100644
--- a/src/test/regress/expected/triggers.out
+++ b/src/test/regress/expected/triggers.out
@@ -476,6 +476,9 @@ NOTICE: dummy_update_func(afterb) called: action = UPDATE, old = (t), new = (f)
UPDATE some_t SET some_col = TRUE;
NOTICE: dummy_update_func(before) called: action = UPDATE, old = (f), new = (t)
NOTICE: dummy_update_func(aftera) called: action = UPDATE, old = (f), new = (t)
+ALTER TABLE some_t ENABLE REPLICA TRIGGER some_trig_aftera;
+ALTER TABLE some_t DISABLE TRIGGER some_trig_afterb;
+ALTER TABLE some_t ENABLE ALWAYS TRIGGER some_trig_before;
CREATE TABLE some_t1 (c INT, LIKE some_t INCLUDING TRIGGERS INCLUDING COMMENTS);
\d+ some_t1
Table "public.some_t1"
@@ -485,10 +488,12 @@ CREATE TABLE some_t1 (c INT, LIKE some_t INCLUDING TRIGGERS INCLUDING COMMENTS);
some_col | boolean | | not null | | plain | |
Not-null constraints:
"some_t_some_col_not_null" NOT NULL "some_col"
-Triggers:
- some_trig_aftera AFTER UPDATE ON some_t1 FOR EACH ROW WHEN (NOT old.some_col AND new.some_col) EXECUTE FUNCTION dummy_update_func('aftera')
+Disabled user triggers:
some_trig_afterb AFTER UPDATE ON some_t1 FOR EACH ROW WHEN (NOT new.some_col) EXECUTE FUNCTION dummy_update_func('afterb')
+Triggers firing always:
some_trig_before BEFORE UPDATE ON some_t1 FOR EACH ROW EXECUTE FUNCTION dummy_update_func('before')
+Triggers firing on replica only:
+ some_trig_aftera AFTER UPDATE ON some_t1 FOR EACH ROW WHEN (NOT old.some_col AND new.some_col) EXECUTE FUNCTION dummy_update_func('aftera')
DROP TABLE some_t;
DROP TABLE some_t1;
diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sql
index 1afaeb863f7..9fb16c72323 100644
--- a/src/test/regress/sql/triggers.sql
+++ b/src/test/regress/sql/triggers.sql
@@ -288,6 +288,9 @@ INSERT INTO some_t VALUES (TRUE);
UPDATE some_t SET some_col = TRUE;
UPDATE some_t SET some_col = FALSE;
UPDATE some_t SET some_col = TRUE;
+ALTER TABLE some_t ENABLE REPLICA TRIGGER some_trig_aftera;
+ALTER TABLE some_t DISABLE TRIGGER some_trig_afterb;
+ALTER TABLE some_t ENABLE ALWAYS TRIGGER some_trig_before;
CREATE TABLE some_t1 (c INT, LIKE some_t INCLUDING TRIGGERS INCLUDING COMMENTS);
\d+ some_t1
DROP TABLE some_t;
--
2.34.1
[application/x-patch] v10-0001-CREATE-TABLE-LIKE-INCLUDING-TRIGGERS.patch (43.8K, 3-v10-0001-CREATE-TABLE-LIKE-INCLUDING-TRIGGERS.patch)
download | inline diff:
From a2463b98c1e4aa8018ec1595cedbf7c17433456a Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Thu, 4 Jun 2026 08:55:03 +0800
Subject: [PATCH v10 1/2] CREATE TABLE LIKE INCLUDING TRIGGERS
This will copy all source table's triggers to the new table. This functionality
also extends to CREATE FOREIGN TABLE LIKE statements.
If INCLUDING COMMENTS is specified, trigger comments will be copied to the new
table.
This command will fail if the source relation's triggers contain whole-row
reference.
Incompatible triggers will be skipped. Internal triggers (such as foreign key
associated triggers) won't being copied to new table. Triggers from a source
view will not be copied to a new table. Additionally, constraint triggers and
triggers with transition tables will not be copied to a new foreign table.
Author: jian he <[email protected]>
Reviewed-by: Andrey Borodin <[email protected]>
Reviewed-by: Zsolt Parragi <[email protected]>
Reviewed-by: Robert Haas <[email protected]>
Reviewed-by: Henson Choi <[email protected]>
Tested-by: lakshmi <[email protected]>
discussion: https://postgr.es/m/CACJufxHJAr2FjbeB6ghg_-N5dxX5JVnjKSLOUxOyt4TeaAWQkg@mail.gmail.com
commitfest: https://commitfest.postgresql.org/patch/6087
---
doc/src/sgml/ref/create_foreign_table.sgml | 17 +-
doc/src/sgml/ref/create_table.sgml | 15 +-
src/backend/catalog/index.c | 2 +
src/backend/commands/tablecmds.c | 8 +
src/backend/commands/trigger.c | 228 +++++++++++++++++-
src/backend/parser/gram.y | 9 +-
src/backend/parser/parse_utilcmd.c | 54 ++++-
src/include/commands/trigger.h | 9 +
src/include/nodes/parsenodes.h | 4 +
src/include/parser/kwlist.h | 1 +
.../regress/expected/create_table_like.out | 23 ++
src/test/regress/expected/triggers.out | 124 ++++++++++
src/test/regress/sql/create_table_like.sql | 24 ++
src/test/regress/sql/triggers.sql | 67 +++++
14 files changed, 572 insertions(+), 13 deletions(-)
diff --git a/doc/src/sgml/ref/create_foreign_table.sgml b/doc/src/sgml/ref/create_foreign_table.sgml
index 083f16772b7..ed59d0a1e0c 100644
--- a/doc/src/sgml/ref/create_foreign_table.sgml
+++ b/doc/src/sgml/ref/create_foreign_table.sgml
@@ -60,7 +60,7 @@ CREATE FOREIGN TABLE [ IF NOT EXISTS ] <replaceable class="parameter">table_name
<phrase>and <replaceable class="parameter">like_option</replaceable> is:</phrase>
-{ INCLUDING | EXCLUDING } { COMMENTS | CONSTRAINTS | DEFAULTS | GENERATED | STATISTICS | ALL }
+{ INCLUDING | EXCLUDING } { COMMENTS | CONSTRAINTS | DEFAULTS | GENERATED | STATISTICS | TRIGGERS | ALL }
<phrase>and <replaceable class="parameter">partition_bound_spec</replaceable> is:</phrase>
@@ -232,7 +232,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<term><literal>INCLUDING COMMENTS</literal></term>
<listitem>
<para>
- Comments for the copied columns, constraints, and extended statistics
+ Comments for the copied columns, constraints, extended statistics, and triggers
will be copied. The default behavior is to exclude comments,
resulting in the corresponding objects in the new table having no
comments.
@@ -284,6 +284,19 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</listitem>
</varlistentry>
+ <varlistentry id="sql-createforeigntable-parms-like-opt-triggers">
+ <term><literal>INCLUDING TRIGGERS</literal></term>
+ <listitem>
+ <para>
+ All non-internal triggers are copied to the new table.
+ Because triggers on views differ significantly from those on tables, triggers from
+ source view are not be copied.
+ Furthermore constraint triggers and triggers that use transition tables
+ will not be copied because foreign tables do not support these.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="sql-createforeigntable-parms-like-opt-all">
<term><literal>INCLUDING ALL</literal></term>
<listitem>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index e342585c7f0..e929da5c551 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -100,7 +100,7 @@ class="parameter">referential_action</replaceable> ] [ ON UPDATE <replaceable cl
<phrase>and <replaceable class="parameter">like_option</replaceable> is:</phrase>
-{ INCLUDING | EXCLUDING } { COMMENTS | COMPRESSION | CONSTRAINTS | DEFAULTS | GENERATED | IDENTITY | INDEXES | STATISTICS | STORAGE | ALL }
+{ INCLUDING | EXCLUDING } { COMMENTS | COMPRESSION | CONSTRAINTS | DEFAULTS | GENERATED | IDENTITY | INDEXES | STATISTICS | STORAGE | TRIGGERS | ALL }
<phrase>and <replaceable class="parameter">partition_bound_spec</replaceable> is:</phrase>
@@ -693,7 +693,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<listitem>
<para>
Comments for the copied columns, check constraints,
- not-null constraints, indexes, and extended statistics will be
+ not-null constraints, indexes, extended statistics, and triggers will be
copied. The default behavior is to exclude comments, resulting in
the corresponding objects in the new table having no
comments.
@@ -797,6 +797,17 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</listitem>
</varlistentry>
+ <varlistentry id="sql-createtable-parms-like-opt-triggers">
+ <term><literal>INCLUDING TRIGGERS</literal></term>
+ <listitem>
+ <para>
+ All non-internal triggers are copied to the new table.
+ Because triggers on views differ significantly from those on tables, triggers from
+ source view are not be copied.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="sql-createtable-parms-like-opt-all">
<term><literal>INCLUDING ALL</literal></term>
<listitem>
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 9407c357f27..6aaaf738662 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -2057,6 +2057,8 @@ index_constraint_create(Relation heapRelation,
trigger->deferrable = true;
trigger->initdeferred = initdeferred;
trigger->constrrel = NULL;
+ trigger->trigcomment = NULL;
+ trigger->transformed = true;
(void) CreateTrigger(trigger, NULL, RelationGetRelid(heapRelation),
InvalidOid, conOid, indexRelationId, InvalidOid,
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index a1845240a98..09654aba5f0 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -14115,6 +14115,8 @@ CreateFKCheckTrigger(Oid myRelOid, Oid refRelOid, Constraint *fkconstraint,
fk_trigger->deferrable = fkconstraint->deferrable;
fk_trigger->initdeferred = fkconstraint->initdeferred;
fk_trigger->constrrel = NULL;
+ fk_trigger->trigcomment = NULL;
+ fk_trigger->transformed = true;
trigAddress = CreateTrigger(fk_trigger, NULL, myRelOid, refRelOid,
constraintOid, indexOid, InvalidOid,
@@ -14160,6 +14162,8 @@ createForeignKeyActionTriggers(Oid myRelOid, Oid refRelOid, Constraint *fkconstr
fk_trigger->whenClause = NULL;
fk_trigger->transitionRels = NIL;
fk_trigger->constrrel = NULL;
+ fk_trigger->trigcomment = NULL;
+ fk_trigger->transformed = true;
switch (fkconstraint->fk_del_action)
{
@@ -14220,6 +14224,8 @@ createForeignKeyActionTriggers(Oid myRelOid, Oid refRelOid, Constraint *fkconstr
fk_trigger->whenClause = NULL;
fk_trigger->transitionRels = NIL;
fk_trigger->constrrel = NULL;
+ fk_trigger->trigcomment = NULL;
+ fk_trigger->transformed = true;
switch (fkconstraint->fk_upd_action)
{
@@ -21214,6 +21220,8 @@ CloneRowTriggersToPartition(Relation parent, Relation partition)
trigStmt->deferrable = trigForm->tgdeferrable;
trigStmt->initdeferred = trigForm->tginitdeferred;
trigStmt->constrrel = NULL; /* passed separately */
+ trigStmt->trigcomment = NULL;
+ trigStmt->transformed = true;
CreateTriggerFiringOn(trigStmt, NULL, RelationGetRelid(partition),
trigForm->tgconstrrelid, InvalidOid, InvalidOid,
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
index b87b4b40d07..f55a08f0088 100644
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -31,6 +31,7 @@
#include "catalog/pg_proc.h"
#include "catalog/pg_trigger.h"
#include "catalog/pg_type.h"
+#include "commands/comment.h"
#include "commands/trigger.h"
#include "executor/executor.h"
#include "executor/instrument.h"
@@ -591,13 +592,19 @@ CreateTriggerFiringOn(const CreateTrigStmt *stmt, const char *queryString,
false, false);
addNSItemToQuery(pstate, nsitem, false, true, true);
- /* Transform expression. Copy to be sure we don't modify original */
- whenClause = transformWhereClause(pstate,
- copyObject(stmt->whenClause),
- EXPR_KIND_TRIGGER_WHEN,
- "WHEN");
- /* we have to fix its collations too */
- assign_expr_collations(pstate, whenClause);
+ if (stmt->transformed)
+ whenClause = stmt->whenClause;
+ else
+ {
+ /* Transform expression. Copy to be sure we don't modify original */
+ whenClause = transformWhereClause(pstate,
+ copyObject(stmt->whenClause),
+ EXPR_KIND_TRIGGER_WHEN,
+ "WHEN");
+
+ /* we have to fix its collations too */
+ assign_expr_collations(pstate, whenClause);
+ }
/*
* Check for disallowed references to OLD/NEW.
@@ -1206,6 +1213,11 @@ CreateTriggerFiringOn(const CreateTrigStmt *stmt, const char *queryString,
/* Keep lock on target rel until end of xact */
table_close(rel, NoLock);
+ /* Add any requested comment */
+ if (stmt->trigcomment != NULL)
+ CreateComments(trigoid, TriggerRelationId, 0,
+ stmt->trigcomment);
+
return myself;
}
@@ -6904,3 +6916,205 @@ AfterTriggerIsActive(void)
{
return afterTriggers.firing_depth > 0;
}
+
+/*
+ * Duplicate the trigger definition for the new relation: Use the source trigger
+ * (source_trigid from source_rel) to initialize a CreateTrigStmt for the target
+ * relation (heapRel).
+ *
+ * Attribute numbers in expression Vars are adjusted according to attmap.
+ */
+CreateTrigStmt *
+generateClonedTriggerStmt(RangeVar *heapRel, Oid source_trigid,
+ Relation source_rel, const AttrMap *attmap)
+{
+ HeapTuple triggerTuple;
+ HeapTuple proctup;
+ Form_pg_trigger trigForm;
+ Form_pg_proc procform;
+ Relation pg_trigger;
+ RangeVar *constrrel = NULL;
+ SysScanDesc tgscan;
+ ScanKeyData skey[1];
+ Datum value;
+ bool isnull;
+ Node *qual = NULL;
+ List *trigargs = NIL;
+ List *cols = NIL;
+ List *funcname = NIL;
+ List *transitionRels = NIL;
+ char *funcschema;
+ CreateTrigStmt *trigStmt;
+
+ pg_trigger = table_open(TriggerRelationId, AccessShareLock);
+
+ /* Find the trigger to copy */
+ ScanKeyInit(&skey[0],
+ Anum_pg_trigger_oid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(source_trigid));
+
+ tgscan = systable_beginscan(pg_trigger, TriggerOidIndexId, true,
+ NULL, 1, skey);
+
+ triggerTuple = systable_getnext(tgscan);
+ if (!HeapTupleIsValid(triggerTuple))
+ elog(ERROR, "could not find tuple for trigger %u", source_trigid);
+
+ trigForm = (Form_pg_trigger) GETSTRUCT(triggerTuple);
+
+ /* Reconstruct trigger function String list */
+ proctup = SearchSysCache1(PROCOID, ObjectIdGetDatum(trigForm->tgfoid));
+ if (!HeapTupleIsValid(proctup))
+ elog(ERROR, "cache lookup failed for function %u", trigForm->tgfoid);
+ procform = (Form_pg_proc) GETSTRUCT(proctup);
+
+ funcschema = get_namespace_name(procform->pronamespace);
+ funcname = list_make2(makeString(funcschema),
+ makeString(pstrdup(NameStr(procform->proname))));
+ ReleaseSysCache(proctup);
+
+ /*
+ * If there is a column list, transform it to a list of column names. Note
+ * we don't need to map this list in any way ...
+ */
+ if (trigForm->tgattr.dim1 > 0)
+ {
+ for (int i = 0; i < trigForm->tgattr.dim1; i++)
+ {
+ Form_pg_attribute col;
+
+ col = TupleDescAttr(RelationGetDescr(source_rel),
+ trigForm->tgattr.values[i] - 1);
+ cols = lappend(cols,
+ makeString(pstrdup(NameStr(col->attname))));
+ }
+ }
+
+ /* Reconstruct trigger arguments list */
+ if (trigForm->tgnargs > 0)
+ {
+ bytea *val;
+ char *p;
+
+ val = DatumGetByteaPP(fastgetattr(triggerTuple,
+ Anum_pg_trigger_tgargs,
+ RelationGetDescr(pg_trigger),
+ &isnull));
+ if (isnull)
+ elog(ERROR, "tgargs is null in trigger \"%s\" for relation \"%s\"",
+ NameStr(trigForm->tgname),
+ RelationGetRelationName(source_rel));
+
+ p = (char *) VARDATA_ANY(val);
+
+ for (int i = 0; i < trigForm->tgnargs; i++)
+ {
+ trigargs = lappend(trigargs, makeString(pstrdup(p)));
+ p += strlen(p) + 1;
+ }
+ }
+
+ /* If the trigger has a WHEN qualification, add that */
+ value = fastgetattr(triggerTuple, Anum_pg_trigger_tgqual,
+ RelationGetDescr(pg_trigger), &isnull);
+ if (!isnull)
+ {
+ bool found_whole_row;
+
+ qual = stringToNode(TextDatumGetCString(value));
+
+ /* Adjust Vars to match new table's column numbering */
+ qual = map_variable_attnos(qual, PRS2_NEW_VARNO, 0,
+ attmap,
+ InvalidOid,
+ &found_whole_row);
+
+ /* As in expandTableLikeClause, reject whole-row variables */
+ if (found_whole_row)
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot convert whole-row table reference"),
+ errdetail("Trigger \"%s\" contains a whole-row table reference.",
+ NameStr(trigForm->tgname)));
+
+ qual = map_variable_attnos(qual, PRS2_OLD_VARNO, 0,
+ attmap,
+ InvalidOid,
+ &found_whole_row);
+
+ /* As in expandTableLikeClause, reject whole-row variables */
+ if (found_whole_row)
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot convert whole-row table reference"),
+ errdetail("Trigger \"%s\" contains a whole-row table reference.",
+ NameStr(trigForm->tgname)));
+ }
+
+ /* Reconstruct trigger old transition table */
+ value = fastgetattr(triggerTuple, Anum_pg_trigger_tgoldtable,
+ RelationGetDescr(pg_trigger), &isnull);
+ if (!isnull)
+ {
+ TriggerTransition *old = makeNode(TriggerTransition);
+
+ old->isNew = false;
+ old->name = pstrdup(NameStr(*DatumGetName(value)));
+ old->isTable = true;
+ transitionRels = lappend(transitionRels, old);
+ }
+
+ /* Reconstruct trigger new transition table */
+ value = fastgetattr(triggerTuple, Anum_pg_trigger_tgnewtable,
+ RelationGetDescr(pg_trigger),
+ &isnull);
+ if (!isnull)
+ {
+ TriggerTransition *new = makeNode(TriggerTransition);
+
+ new->isNew = true;
+ new->name = pstrdup(NameStr(*DatumGetName(value)));
+ new->isTable = true;
+ transitionRels = lappend(transitionRels, new);
+ }
+
+ /* Reconstruct trigger constraint's FROM table */
+ if (OidIsValid(trigForm->tgconstrrelid))
+ {
+ /*
+ * Acquire the AccessShareLock lock on tgconstrrelid now, as it will
+ * be required later in CreateTriggerFiringOn.
+ */
+ LockRelationOid(trigForm->tgconstrrelid, AccessShareLock);
+
+ constrrel =
+ makeRangeVar(get_namespace_name(get_rel_namespace(trigForm->tgconstrrelid)),
+ get_rel_name(trigForm->tgconstrrelid),
+ -1);
+ }
+
+ trigStmt = makeNode(CreateTrigStmt);
+ trigStmt->replace = false;
+ trigStmt->isconstraint = OidIsValid(trigForm->tgconstraint);
+ trigStmt->trigname = pstrdup(NameStr(trigForm->tgname));
+ trigStmt->relation = heapRel;
+ trigStmt->funcname = funcname;
+ trigStmt->args = trigargs;
+ trigStmt->row = TRIGGER_FOR_ROW(trigForm->tgtype);
+ trigStmt->timing = trigForm->tgtype & TRIGGER_TYPE_TIMING_MASK;
+ trigStmt->events = trigForm->tgtype & TRIGGER_TYPE_EVENT_MASK;
+ trigStmt->columns = cols;
+ trigStmt->whenClause = qual;
+ trigStmt->transitionRels = transitionRels;
+ trigStmt->deferrable = trigForm->tgdeferrable;
+ trigStmt->initdeferred = trigForm->tginitdeferred;
+ trigStmt->constrrel = constrrel;
+ trigStmt->trigcomment = NULL;
+ trigStmt->transformed = true;
+
+ systable_endscan(tgscan);
+ table_close(pg_trigger, AccessShareLock);
+
+ return trigStmt;
+}
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index ff4e1388c55..6fca2636116 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -822,7 +822,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
TABLE TABLES TABLESAMPLE TABLESPACE TARGET TEMP TEMPLATE TEMPORARY TEXT_P THEN
TIES TIME TIMESTAMP TO TRAILING TRANSACTION TRANSFORM
- TREAT TRIGGER TRIM TRUE_P
+ TREAT TRIGGER TRIGGERS TRIM TRUE_P
TRUNCATE TRUSTED TYPE_P TYPES_P
UESCAPE UNBOUNDED UNCONDITIONAL UNCOMMITTED UNENCRYPTED UNION UNIQUE UNKNOWN
@@ -4353,6 +4353,7 @@ TableLikeOption:
| INDEXES { $$ = CREATE_TABLE_LIKE_INDEXES; }
| STATISTICS { $$ = CREATE_TABLE_LIKE_STATISTICS; }
| STORAGE { $$ = CREATE_TABLE_LIKE_STORAGE; }
+ | TRIGGERS { $$ = CREATE_TABLE_LIKE_TRIGGERS; }
| ALL { $$ = CREATE_TABLE_LIKE_ALL; }
;
@@ -6209,6 +6210,8 @@ CreateTrigStmt:
n->deferrable = false;
n->initdeferred = false;
n->constrrel = NULL;
+ n->trigcomment = NULL;
+ n->transformed = false;
$$ = (Node *) n;
}
| CREATE opt_or_replace CONSTRAINT TRIGGER name AFTER TriggerEvents ON
@@ -6259,6 +6262,8 @@ CreateTrigStmt:
&n->deferrable, &n->initdeferred, &dummy,
NULL, NULL, yyscanner);
n->constrrel = $10;
+ n->trigcomment = NULL;
+ n->transformed = false;
$$ = (Node *) n;
}
;
@@ -19130,6 +19135,7 @@ unreserved_keyword:
| TRANSACTION
| TRANSFORM
| TRIGGER
+ | TRIGGERS
| TRUNCATE
| TRUSTED
| TYPE_P
@@ -19795,6 +19801,7 @@ bare_label_keyword:
| TRANSFORM
| TREAT
| TRIGGER
+ | TRIGGERS
| TRIM
| TRUE_P
| TRUNCATE
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index a049cc67ed6..9fefb9dc698 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -40,12 +40,14 @@
#include "catalog/pg_opclass.h"
#include "catalog/pg_operator.h"
#include "catalog/pg_statistic_ext.h"
+#include "catalog/pg_trigger.h"
#include "catalog/pg_type.h"
#include "commands/comment.h"
#include "commands/defrem.h"
#include "commands/sequence.h"
#include "commands/tablecmds.h"
#include "commands/tablespace.h"
+#include "commands/trigger.h"
#include "miscadmin.h"
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
@@ -1316,7 +1318,8 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
CREATE_TABLE_LIKE_GENERATED |
CREATE_TABLE_LIKE_CONSTRAINTS |
CREATE_TABLE_LIKE_INDEXES |
- CREATE_TABLE_LIKE_STATISTICS))
+ CREATE_TABLE_LIKE_STATISTICS |
+ CREATE_TABLE_LIKE_TRIGGERS))
{
table_like_clause->relationOid = RelationGetRelid(relation);
cxt->likeclauses = lappend(cxt->likeclauses, table_like_clause);
@@ -1620,6 +1623,55 @@ expandTableLikeClause(RangeVar *heapRel, TableLikeClause *table_like_clause)
list_free(parent_extstats);
}
+ /* Process triggers if required */
+ if ((table_like_clause->options & CREATE_TABLE_LIKE_TRIGGERS) &&
+ (relation->trigdesc != NULL) &&
+ (relation->rd_rel->relkind == RELKIND_RELATION ||
+ relation->rd_rel->relkind == RELKIND_PARTITIONED_TABLE ||
+ relation->rd_rel->relkind == RELKIND_FOREIGN_TABLE))
+ {
+ bool include_comments;
+
+ include_comments = (table_like_clause->options & CREATE_TABLE_LIKE_COMMENTS);
+
+ for (int nt = 0; nt < relation->trigdesc->numtriggers; nt++)
+ {
+ CreateTrigStmt *trig_stmt;
+ Trigger *trig = relation->trigdesc->triggers + nt;
+ Oid trigoid = trig->tgoid;
+
+ /* We do not copy internal trigger to the new table */
+ if (trig->tgisinternal)
+ continue;
+
+ /*
+ * Foreign table cannot have constraint trigger or triggers with
+ * transition table
+ */
+ if (childrel->rd_rel->relkind == RELKIND_FOREIGN_TABLE)
+ {
+ if (OidIsValid(trig->tgconstraint))
+ continue;
+
+ if (trig->tgoldtable != NULL || trig->tgnewtable != NULL)
+ continue;
+ }
+
+ trig_stmt = generateClonedTriggerStmt(heapRel, trigoid,
+ relation, attmap);
+
+ /* Copy comment on trigger, if requested */
+ if (include_comments)
+ {
+ comment = GetComment(trigoid, TriggerRelationId, 0);
+
+ /* We make use of CreateTrigStmt's trigcomment option */
+ trig_stmt->trigcomment = comment;
+ }
+ result = lappend(result, trig_stmt);
+ }
+ }
+
/* Done with child rel */
table_close(childrel, NoLock);
diff --git a/src/include/commands/trigger.h b/src/include/commands/trigger.h
index 1d9869973c0..ce4b3cf1396 100644
--- a/src/include/commands/trigger.h
+++ b/src/include/commands/trigger.h
@@ -18,6 +18,11 @@
#include "nodes/execnodes.h"
#include "nodes/parsenodes.h"
+/*
+ * forward references in this file
+ */
+typedef struct AttrMap AttrMap;
+
/*
* TriggerData is the node type that is passed as fmgr "context" info
* when a function is called by the trigger manager.
@@ -268,6 +273,10 @@ extern void AfterTriggerBeginSubXact(void);
extern void AfterTriggerEndSubXact(bool isCommit);
extern void AfterTriggerSetState(ConstraintsSetStmt *stmt);
extern bool AfterTriggerPendingOnRel(Oid relid);
+extern CreateTrigStmt *generateClonedTriggerStmt(RangeVar *heapRel,
+ Oid source_trigid,
+ Relation source_rel,
+ const AttrMap *attmap);
/*
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 91377a6cde3..a7d01f5f9fd 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -811,6 +811,7 @@ typedef enum TableLikeOption
CREATE_TABLE_LIKE_INDEXES = 1 << 6,
CREATE_TABLE_LIKE_STATISTICS = 1 << 7,
CREATE_TABLE_LIKE_STORAGE = 1 << 8,
+ CREATE_TABLE_LIKE_TRIGGERS = 1 << 9,
CREATE_TABLE_LIKE_ALL = PG_INT32_MAX
} TableLikeOption;
@@ -3234,6 +3235,9 @@ typedef struct CreateTrigStmt
bool deferrable; /* [NOT] DEFERRABLE */
bool initdeferred; /* INITIALLY {DEFERRED|IMMEDIATE} */
RangeVar *constrrel; /* opposite relation, if RI trigger */
+ char *trigcomment; /* comment to apply to trigger, or NULL */
+ bool transformed; /* true means no need to do parse analysis for
+ * whenClause */
} CreateTrigStmt;
/* ----------------------
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 51ead54f015..9733d4a796c 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -474,6 +474,7 @@ PG_KEYWORD("transaction", TRANSACTION, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("transform", TRANSFORM, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("treat", TREAT, COL_NAME_KEYWORD, BARE_LABEL)
PG_KEYWORD("trigger", TRIGGER, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("triggers", TRIGGERS, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("trim", TRIM, COL_NAME_KEYWORD, BARE_LABEL)
PG_KEYWORD("true", TRUE_P, RESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("truncate", TRUNCATE, UNRESERVED_KEYWORD, BARE_LABEL)
diff --git a/src/test/regress/expected/create_table_like.out b/src/test/regress/expected/create_table_like.out
index 76069bde756..4f3214e4e91 100644
--- a/src/test/regress/expected/create_table_like.out
+++ b/src/test/regress/expected/create_table_like.out
@@ -635,6 +635,26 @@ Not-null constraints:
"ctl_table_a_not_null" NOT NULL "a"
"ctl_table_d_not_null" NOT NULL "d"
+CREATE OR REPLACE FUNCTION trigger_func() RETURNS trigger LANGUAGE plpgsql AS '
+BEGIN
+ RAISE NOTICE ''trigger_func(%) called: action = %, when = %, level = %'', TG_ARGV[0], TG_OP, TG_WHEN, TG_LEVEL;
+ RETURN NULL;
+END;';
+CREATE TRIGGER trigtest_before_stmt BEFORE DELETE OR UPDATE ON ctl_table
+FOR EACH ROW WHEN (OLD.a > 0)
+EXECUTE PROCEDURE trigger_func('trigtest_before_stmt');
+CREATE TRIGGER trigtest_after_stmt AFTER UPDATE OF a, b ON ctl_table
+FOR EACH STATEMENT
+EXECUTE PROCEDURE trigger_func('trigtest_before_stmt');
+-- CREATE FOREIGN TABLE LIKE should not copy triggers that have transition tables
+CREATE TRIGGER trigtest_after_stmt1 AFTER INSERT ON ctl_table
+REFERENCING NEW TABLE AS new_table
+FOR EACH STATEMENT
+EXECUTE PROCEDURE trigger_func('trigtest_after_stmt1');
+-- CREATE FOREIGN TABLE LIKE should not copy source table cosntraint trigger
+CREATE CONSTRAINT TRIGGER constr_trig
+ AFTER INSERT ON ctl_table DEFERRABLE INITIALLY DEFERRED enforced FOR EACH ROW
+ EXECUTE PROCEDURE trigger_func ();
-- Test EXCLUDING ALL
CREATE FOREIGN TABLE ctl_foreign_table1(LIKE ctl_table EXCLUDING ALL) SERVER ctl_s0;
\d+ ctl_foreign_table1
@@ -683,6 +703,9 @@ Statistics objects:
Not-null constraints:
"ctl_table_a_not_null" NOT NULL "a"
"ctl_table_d_not_null" NOT NULL "d"
+Triggers:
+ trigtest_after_stmt AFTER UPDATE OF a, b ON ctl_foreign_table2 FOR EACH STATEMENT EXECUTE FUNCTION trigger_func('trigtest_before_stmt')
+ trigtest_before_stmt BEFORE DELETE OR UPDATE ON ctl_foreign_table2 FOR EACH ROW WHEN (old.a > 0) EXECUTE FUNCTION trigger_func('trigtest_before_stmt')
Server: ctl_s0
-- \d+ does not report the value of attcompression for a foreign table, so
diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out
index 8fcb33ac81a..f675786739d 100644
--- a/src/test/regress/expected/triggers.out
+++ b/src/test/regress/expected/triggers.out
@@ -174,6 +174,20 @@ CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON main_table
FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('before_ins_stmt');
CREATE TRIGGER after_ins_stmt_trig AFTER INSERT ON main_table
FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('after_ins_stmt');
+CREATE TRIGGER wholetrig BEFORE UPDATE ON main_table FOR EACH ROW
+WHEN (OLD IS NOT NULL)
+EXECUTE PROCEDURE trigger_func('modified_a');
+CREATE TABLE main_table1(LIKE main_table INCLUDING TRIGGERS); -- error, wholerow reference not allowed
+ERROR: cannot convert whole-row table reference
+DETAIL: Trigger "wholetrig" contains a whole-row table reference.
+DROP TRIGGER wholetrig ON main_table;
+CREATE TRIGGER wholetrig BEFORE UPDATE ON main_table FOR EACH ROW
+WHEN (NEW IS NOT NULL)
+EXECUTE PROCEDURE trigger_func('modified_a');
+CREATE TABLE main_table1(LIKE main_table INCLUDING TRIGGERS); -- error, wholerow reference not allowed
+ERROR: cannot convert whole-row table reference
+DETAIL: Trigger "wholetrig" contains a whole-row table reference.
+DROP TRIGGER wholetrig ON main_table;
--
-- if neither 'FOR EACH ROW' nor 'FOR EACH STATEMENT' was specified,
-- CREATE TRIGGER should default to 'FOR EACH STATEMENT'
@@ -394,6 +408,46 @@ NOTICE: trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER,
NOTICE: trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW
NOTICE: trigger_func(after_upd_b_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT
NOTICE: trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT
+-- CREATE TABLE LIKE INCLUDING TRIGGERS tests
+COMMENT ON TRIGGER before_ins_stmt_trig ON main_table IS 'trigger before_ins_stmt_trig';
+CREATE TABLE main_table1(c int, LIKE main_table INCLUDING TRIGGERS INCLUDING COMMENTS);
+CREATE TABLE main_table2(c int, LIKE main_table EXCLUDING TRIGGERS INCLUDING COMMENTS);
+SELECT pg_get_triggerdef(oid)
+FROM pg_trigger
+WHERE NOT tgisinternal AND tgrelid IN ('main_table'::regclass, 'main_table1'::regclass, 'main_table2'::regclass)
+ORDER BY tgname, tgrelid::regclass::text COLLATE "C";
+ pg_get_triggerdef
+------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE TRIGGER after_ins_stmt_trig AFTER INSERT ON public.main_table FOR EACH STATEMENT EXECUTE FUNCTION trigger_func('after_ins_stmt')
+ CREATE TRIGGER after_ins_stmt_trig AFTER INSERT ON public.main_table1 FOR EACH STATEMENT EXECUTE FUNCTION trigger_func('after_ins_stmt')
+ CREATE TRIGGER after_upd_a_b_row_trig AFTER UPDATE OF a, b ON public.main_table FOR EACH ROW EXECUTE FUNCTION trigger_func('after_upd_a_b_row')
+ CREATE TRIGGER after_upd_a_b_row_trig AFTER UPDATE OF a, b ON public.main_table1 FOR EACH ROW EXECUTE FUNCTION trigger_func('after_upd_a_b_row')
+ CREATE TRIGGER after_upd_b_row_trig AFTER UPDATE OF b ON public.main_table FOR EACH ROW EXECUTE FUNCTION trigger_func('after_upd_b_row')
+ CREATE TRIGGER after_upd_b_row_trig AFTER UPDATE OF b ON public.main_table1 FOR EACH ROW EXECUTE FUNCTION trigger_func('after_upd_b_row')
+ CREATE TRIGGER after_upd_b_stmt_trig AFTER UPDATE OF b ON public.main_table FOR EACH STATEMENT EXECUTE FUNCTION trigger_func('after_upd_b_stmt')
+ CREATE TRIGGER after_upd_b_stmt_trig AFTER UPDATE OF b ON public.main_table1 FOR EACH STATEMENT EXECUTE FUNCTION trigger_func('after_upd_b_stmt')
+ CREATE TRIGGER after_upd_stmt_trig AFTER UPDATE ON public.main_table FOR EACH STATEMENT EXECUTE FUNCTION trigger_func('after_upd_stmt')
+ CREATE TRIGGER after_upd_stmt_trig AFTER UPDATE ON public.main_table1 FOR EACH STATEMENT EXECUTE FUNCTION trigger_func('after_upd_stmt')
+ CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON public.main_table FOR EACH STATEMENT EXECUTE FUNCTION trigger_func('before_ins_stmt')
+ CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON public.main_table1 FOR EACH STATEMENT EXECUTE FUNCTION trigger_func('before_ins_stmt')
+ CREATE TRIGGER before_upd_a_row_trig BEFORE UPDATE OF a ON public.main_table FOR EACH ROW EXECUTE FUNCTION trigger_func('before_upd_a_row')
+ CREATE TRIGGER before_upd_a_row_trig BEFORE UPDATE OF a ON public.main_table1 FOR EACH ROW EXECUTE FUNCTION trigger_func('before_upd_a_row')
+ CREATE TRIGGER before_upd_a_stmt_trig BEFORE UPDATE OF a ON public.main_table FOR EACH STATEMENT EXECUTE FUNCTION trigger_func('before_upd_a_stmt')
+ CREATE TRIGGER before_upd_a_stmt_trig BEFORE UPDATE OF a ON public.main_table1 FOR EACH STATEMENT EXECUTE FUNCTION trigger_func('before_upd_a_stmt')
+(16 rows)
+
+SELECT pc.relname, pd.tgname, obj_description(pd.oid, 'pg_trigger')
+FROM pg_trigger pd JOIN pg_class pc
+ON pc.oid = pd.tgrelid AND pd.tgname = 'before_ins_stmt_trig'
+ORDER BY 1;
+ relname | tgname | obj_description
+-------------+----------------------+------------------------------
+ main_table | before_ins_stmt_trig | trigger before_ins_stmt_trig
+ main_table1 | before_ins_stmt_trig | trigger before_ins_stmt_trig
+(2 rows)
+
+COMMENT ON TRIGGER before_ins_stmt_trig ON main_table IS NULL;
+DROP TABLE main_table1, main_table2;
--
-- Test case for bug with BEFORE trigger followed by AFTER trigger with WHEN
--
@@ -422,7 +476,22 @@ NOTICE: dummy_update_func(afterb) called: action = UPDATE, old = (t), new = (f)
UPDATE some_t SET some_col = TRUE;
NOTICE: dummy_update_func(before) called: action = UPDATE, old = (f), new = (t)
NOTICE: dummy_update_func(aftera) called: action = UPDATE, old = (f), new = (t)
+CREATE TABLE some_t1 (c INT, LIKE some_t INCLUDING TRIGGERS INCLUDING COMMENTS);
+\d+ some_t1
+ Table "public.some_t1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+----------+---------+-----------+----------+---------+---------+--------------+-------------
+ c | integer | | | | plain | |
+ some_col | boolean | | not null | | plain | |
+Not-null constraints:
+ "some_t_some_col_not_null" NOT NULL "some_col"
+Triggers:
+ some_trig_aftera AFTER UPDATE ON some_t1 FOR EACH ROW WHEN (NOT old.some_col AND new.some_col) EXECUTE FUNCTION dummy_update_func('aftera')
+ some_trig_afterb AFTER UPDATE ON some_t1 FOR EACH ROW WHEN (NOT new.some_col) EXECUTE FUNCTION dummy_update_func('afterb')
+ some_trig_before BEFORE UPDATE ON some_t1 FOR EACH ROW EXECUTE FUNCTION dummy_update_func('before')
+
DROP TABLE some_t;
+DROP TABLE some_t1;
-- bogus cases
CREATE TRIGGER error_upd_and_col BEFORE UPDATE OR UPDATE OF a ON main_table
FOR EACH ROW EXECUTE PROCEDURE trigger_func('error_upd_and_col');
@@ -861,6 +930,15 @@ CREATE TRIGGER instead_of_update_trig INSTEAD OF UPDATE ON main_view
FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_upd');
CREATE TRIGGER instead_of_delete_trig INSTEAD OF DELETE ON main_view
FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_del');
+CREATE TABLE main_view_table(LIKE main_view INCLUDING TRIGGERS); -- View triggers are not copied
+\d main_view_table
+ Table "public.main_view_table"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+ b | integer | | |
+
+DROP TABLE main_view_table;
-- Valid BEFORE statement VIEW triggers
CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON main_view
FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_ins_stmt');
@@ -2326,6 +2404,20 @@ create constraint trigger parted_trig_two after insert on parted_constr
deferrable initially deferred enforced
for each row when (bark(new.b) AND new.a % 2 = 1)
execute procedure trigger_notice_ab();
+create table parted_constr_copy (like parted_constr including all);
+select pg_get_triggerdef(oid)
+from pg_trigger
+where not tgisinternal and tgrelid in ('parted_constr_copy'::regclass, 'parted_constr'::regclass)
+order by tgname;
+ pg_get_triggerdef
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE CONSTRAINT TRIGGER parted_trig AFTER INSERT ON public.parted_constr DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE FUNCTION trigger_notice_ab()
+ CREATE CONSTRAINT TRIGGER parted_trig AFTER INSERT ON public.parted_constr_copy DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE FUNCTION trigger_notice_ab()
+ CREATE CONSTRAINT TRIGGER parted_trig_two AFTER INSERT ON public.parted_constr DEFERRABLE INITIALLY DEFERRED FOR EACH ROW WHEN ((bark(new.b) AND ((new.a % 2) = 1))) EXECUTE FUNCTION trigger_notice_ab()
+ CREATE CONSTRAINT TRIGGER parted_trig_two AFTER INSERT ON public.parted_constr_copy DEFERRABLE INITIALLY DEFERRED FOR EACH ROW WHEN ((bark(new.b) AND ((new.a % 2) = 1))) EXECUTE FUNCTION trigger_notice_ab()
+(4 rows)
+
+drop table parted_constr_copy;
-- The immediate constraint is fired immediately; the WHEN clause of the
-- deferred constraint is also called immediately. The deferred constraint
-- is fired at commit time.
@@ -3106,6 +3198,18 @@ create trigger iocdu_tt_parted_insert_trig
create trigger iocdu_tt_parted_update_trig
after update on iocdu_tt_parted referencing old table as old_table new table as new_table
for each statement execute procedure dump_update();
+CREATE TABLE iocdu_tt_parted_copy(LIKE iocdu_tt_parted INCLUDING TRIGGERS);
+\d iocdu_tt_parted_copy
+ Table "public.iocdu_tt_parted_copy"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | not null |
+ b | text | | |
+Triggers:
+ iocdu_tt_parted_insert_trig AFTER INSERT ON iocdu_tt_parted_copy REFERENCING NEW TABLE AS new_table FOR EACH STATEMENT EXECUTE FUNCTION dump_insert()
+ iocdu_tt_parted_update_trig AFTER UPDATE ON iocdu_tt_parted_copy REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table FOR EACH STATEMENT EXECUTE FUNCTION dump_update()
+
+DROP TABLE iocdu_tt_parted_copy;
-- inserts only
insert into iocdu_tt_parted values (1, 'AAA'), (2, 'BBB')
on conflict (a) do
@@ -3589,6 +3693,26 @@ begin
end;
$$;
alter function whoami() owner to regress_fn_owner;
+-- CREATE TABLE LIKE INCLUDING TRIGGERS for constraint trigger
+create schema test_trig;
+create table test_trig.trig_t1 (id integer);
+create table test_trig.trig_t2 (id integer);
+alter function whoami() set schema test_trig;
+create constraint trigger con_trig_test after insert on test_trig.trig_t1
+from test_trig.trig_t2
+deferrable initially deferred
+for each row
+execute function test_trig.whoami();
+create table trig_t1_copy(like test_trig.trig_t1 including triggers);
+select pg_get_triggerdef(oid) from pg_trigger where tgrelid = 'trig_t1_copy'::regclass;
+ pg_get_triggerdef
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE CONSTRAINT TRIGGER con_trig_test AFTER INSERT ON public.trig_t1_copy FROM test_trig.trig_t2 DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION test_trig.whoami()
+(1 row)
+
+alter function test_trig.whoami() set schema public;
+drop table test_trig.trig_t1, test_trig.trig_t2, trig_t1_copy;
+drop schema test_trig;
create table defer_trig (id integer);
grant insert on defer_trig to public;
create constraint trigger whoami after insert on defer_trig
diff --git a/src/test/regress/sql/create_table_like.sql b/src/test/regress/sql/create_table_like.sql
index d52a93ef131..c2d8acf6b6f 100644
--- a/src/test/regress/sql/create_table_like.sql
+++ b/src/test/regress/sql/create_table_like.sql
@@ -259,6 +259,30 @@ ALTER TABLE ctl_table ALTER COLUMN b SET STORAGE MAIN;
\d+ ctl_table
+CREATE OR REPLACE FUNCTION trigger_func() RETURNS trigger LANGUAGE plpgsql AS '
+BEGIN
+ RAISE NOTICE ''trigger_func(%) called: action = %, when = %, level = %'', TG_ARGV[0], TG_OP, TG_WHEN, TG_LEVEL;
+ RETURN NULL;
+END;';
+CREATE TRIGGER trigtest_before_stmt BEFORE DELETE OR UPDATE ON ctl_table
+FOR EACH ROW WHEN (OLD.a > 0)
+EXECUTE PROCEDURE trigger_func('trigtest_before_stmt');
+
+CREATE TRIGGER trigtest_after_stmt AFTER UPDATE OF a, b ON ctl_table
+FOR EACH STATEMENT
+EXECUTE PROCEDURE trigger_func('trigtest_before_stmt');
+
+-- CREATE FOREIGN TABLE LIKE should not copy triggers that have transition tables
+CREATE TRIGGER trigtest_after_stmt1 AFTER INSERT ON ctl_table
+REFERENCING NEW TABLE AS new_table
+FOR EACH STATEMENT
+EXECUTE PROCEDURE trigger_func('trigtest_after_stmt1');
+
+-- CREATE FOREIGN TABLE LIKE should not copy source table cosntraint trigger
+CREATE CONSTRAINT TRIGGER constr_trig
+ AFTER INSERT ON ctl_table DEFERRABLE INITIALLY DEFERRED enforced FOR EACH ROW
+ EXECUTE PROCEDURE trigger_func ();
+
-- Test EXCLUDING ALL
CREATE FOREIGN TABLE ctl_foreign_table1(LIKE ctl_table EXCLUDING ALL) SERVER ctl_s0;
\d+ ctl_foreign_table1
diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sql
index 2285e90110e..1afaeb863f7 100644
--- a/src/test/regress/sql/triggers.sql
+++ b/src/test/regress/sql/triggers.sql
@@ -120,6 +120,18 @@ FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('before_ins_stmt');
CREATE TRIGGER after_ins_stmt_trig AFTER INSERT ON main_table
FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('after_ins_stmt');
+CREATE TRIGGER wholetrig BEFORE UPDATE ON main_table FOR EACH ROW
+WHEN (OLD IS NOT NULL)
+EXECUTE PROCEDURE trigger_func('modified_a');
+CREATE TABLE main_table1(LIKE main_table INCLUDING TRIGGERS); -- error, wholerow reference not allowed
+DROP TRIGGER wholetrig ON main_table;
+
+CREATE TRIGGER wholetrig BEFORE UPDATE ON main_table FOR EACH ROW
+WHEN (NEW IS NOT NULL)
+EXECUTE PROCEDURE trigger_func('modified_a');
+CREATE TABLE main_table1(LIKE main_table INCLUDING TRIGGERS); -- error, wholerow reference not allowed
+DROP TRIGGER wholetrig ON main_table;
+
--
-- if neither 'FOR EACH ROW' nor 'FOR EACH STATEMENT' was specified,
-- CREATE TRIGGER should default to 'FOR EACH STATEMENT'
@@ -234,6 +246,24 @@ SELECT pg_get_triggerdef(oid) FROM pg_trigger WHERE tgrelid = 'main_table'::regc
UPDATE main_table SET a = 50;
UPDATE main_table SET b = 10;
+-- CREATE TABLE LIKE INCLUDING TRIGGERS tests
+COMMENT ON TRIGGER before_ins_stmt_trig ON main_table IS 'trigger before_ins_stmt_trig';
+CREATE TABLE main_table1(c int, LIKE main_table INCLUDING TRIGGERS INCLUDING COMMENTS);
+CREATE TABLE main_table2(c int, LIKE main_table EXCLUDING TRIGGERS INCLUDING COMMENTS);
+
+SELECT pg_get_triggerdef(oid)
+FROM pg_trigger
+WHERE NOT tgisinternal AND tgrelid IN ('main_table'::regclass, 'main_table1'::regclass, 'main_table2'::regclass)
+ORDER BY tgname, tgrelid::regclass::text COLLATE "C";
+
+SELECT pc.relname, pd.tgname, obj_description(pd.oid, 'pg_trigger')
+FROM pg_trigger pd JOIN pg_class pc
+ON pc.oid = pd.tgrelid AND pd.tgname = 'before_ins_stmt_trig'
+ORDER BY 1;
+COMMENT ON TRIGGER before_ins_stmt_trig ON main_table IS NULL;
+
+DROP TABLE main_table1, main_table2;
+
--
-- Test case for bug with BEFORE trigger followed by AFTER trigger with WHEN
--
@@ -258,7 +288,10 @@ INSERT INTO some_t VALUES (TRUE);
UPDATE some_t SET some_col = TRUE;
UPDATE some_t SET some_col = FALSE;
UPDATE some_t SET some_col = TRUE;
+CREATE TABLE some_t1 (c INT, LIKE some_t INCLUDING TRIGGERS INCLUDING COMMENTS);
+\d+ some_t1
DROP TABLE some_t;
+DROP TABLE some_t1;
-- bogus cases
CREATE TRIGGER error_upd_and_col BEFORE UPDATE OR UPDATE OF a ON main_table
@@ -621,6 +654,10 @@ FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_upd');
CREATE TRIGGER instead_of_delete_trig INSTEAD OF DELETE ON main_view
FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_del');
+CREATE TABLE main_view_table(LIKE main_view INCLUDING TRIGGERS); -- View triggers are not copied
+\d main_view_table
+DROP TABLE main_view_table;
+
-- Valid BEFORE statement VIEW triggers
CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON main_view
FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_ins_stmt');
@@ -1609,6 +1646,13 @@ create constraint trigger parted_trig_two after insert on parted_constr
for each row when (bark(new.b) AND new.a % 2 = 1)
execute procedure trigger_notice_ab();
+create table parted_constr_copy (like parted_constr including all);
+select pg_get_triggerdef(oid)
+from pg_trigger
+where not tgisinternal and tgrelid in ('parted_constr_copy'::regclass, 'parted_constr'::regclass)
+order by tgname;
+drop table parted_constr_copy;
+
-- The immediate constraint is fired immediately; the WHEN clause of the
-- deferred constraint is also called immediately. The deferred constraint
-- is fired at commit time.
@@ -2283,6 +2327,10 @@ create trigger iocdu_tt_parted_update_trig
after update on iocdu_tt_parted referencing old table as old_table new table as new_table
for each statement execute procedure dump_update();
+CREATE TABLE iocdu_tt_parted_copy(LIKE iocdu_tt_parted INCLUDING TRIGGERS);
+\d iocdu_tt_parted_copy
+DROP TABLE iocdu_tt_parted_copy;
+
-- inserts only
insert into iocdu_tt_parted values (1, 'AAA'), (2, 'BBB')
on conflict (a) do
@@ -2740,6 +2788,25 @@ end;
$$;
alter function whoami() owner to regress_fn_owner;
+-- CREATE TABLE LIKE INCLUDING TRIGGERS for constraint trigger
+create schema test_trig;
+create table test_trig.trig_t1 (id integer);
+create table test_trig.trig_t2 (id integer);
+alter function whoami() set schema test_trig;
+
+create constraint trigger con_trig_test after insert on test_trig.trig_t1
+from test_trig.trig_t2
+deferrable initially deferred
+for each row
+execute function test_trig.whoami();
+
+create table trig_t1_copy(like test_trig.trig_t1 including triggers);
+select pg_get_triggerdef(oid) from pg_trigger where tgrelid = 'trig_t1_copy'::regclass;
+
+alter function test_trig.whoami() set schema public;
+drop table test_trig.trig_t1, test_trig.trig_t2, trig_t1_copy;
+drop schema test_trig;
+
create table defer_trig (id integer);
grant insert on defer_trig to public;
create constraint trigger whoami after insert on defer_trig
--
2.34.1
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: CREATE TABLE LIKE INCLUDING TRIGGERS
2026-01-16 07:34 Re: CREATE TABLE LIKE INCLUDING TRIGGERS jian he <[email protected]>
2026-01-21 12:13 ` Re: CREATE TABLE LIKE INCLUDING TRIGGERS Zsolt Parragi <[email protected]>
2026-01-22 06:08 ` Re: CREATE TABLE LIKE INCLUDING TRIGGERS jian he <[email protected]>
2026-01-22 14:20 ` Re: CREATE TABLE LIKE INCLUDING TRIGGERS Zsolt Parragi <[email protected]>
2026-02-05 06:15 ` Re: CREATE TABLE LIKE INCLUDING TRIGGERS jian he <[email protected]>
2026-02-06 14:49 ` Re: CREATE TABLE LIKE INCLUDING TRIGGERS Zsolt Parragi <[email protected]>
2026-02-09 07:43 ` Re: CREATE TABLE LIKE INCLUDING TRIGGERS jian he <[email protected]>
@ 2026-06-04 02:10 ` jian he <[email protected]>
2 siblings, 0 replies; 12+ messages in thread
From: jian he @ 2026-06-04 02:10 UTC (permalink / raw)
To: [email protected]; +Cc: Zsolt Parragi <[email protected]>; Andrey Borodin <[email protected]>; pgsql-hackers
On Thu, May 7, 2026 at 9:38 AM Henson Choi <[email protected]> wrote:
>
> MINOR ISSUES
> ------------
>
> 1. Test: cross-file dependency on trigger_func (create_table_like.sql)
>
> create_table_like.sql references trigger_func without creating it,
> relying on triggers.sql having run first. The dependency is noted
> in a comment, and parallel_schedule guarantees the correct order for
> a full "make check" run. However, "make check TESTS=create_table_like"
> will fail because trigger_func does not exist.
>
> PostgreSQL convention is that each test file creates and drops the
> objects it needs. Please add a local definition of trigger_func in
> create_table_like.sql (and drop it at the end of the new block).
>
OK.
> 2. Test: EXCLUDING TRIGGERS not exercised
>
> The grammar now accepts EXCLUDING TRIGGERS, but no test uses it.
> The default (no option) is equivalent, but a one-line smoke test
> would confirm the keyword is accepted and has the expected effect:
>
> CREATE TABLE t_excl (LIKE source_table EXCLUDING TRIGGERS);
>
OK.
> 3. Documentation: create_table.sgml wording inconsistent with
> create_foreign_table.sgml
>
> create_foreign_table.sgml already reads:
>
> "All non-internal triggers are copied to the new table."
>
> create_table.sgml reads:
>
> "All non-internal triggers on the original table will be created
> on the new table."
>
> Recommended wording for create_table.sgml:
>
> "All non-internal triggers are copied to the new table."
>
OK.
> 4. Code comment: capitalisation in generateClonedTriggerStmt()
> (trigger.c)
>
> /* Reconstruct trigger function String list */
>
> "String" should be lowercase "string".
>
Other places also have "String list", it refers to the T_String node type.
> 5. Code comment: overly verbose in expandTableLikeClause()
> (parse_utilcmd.c)
>
> /* We make use of CreateTrigStmt's trigcomment option */
>
> The code is self-explanatory. I would recommend removing it or
> replacing it with something like:
>
> /* pass comment through to CreateTrigger */
>
Other places in parse_utilcmd.c have:
/*
* We make use of IndexStmt's idxcomment option, so as not to
* need to know now what name the index will have.
*/
/*
* We make use of CreateStatsStmt's stxcomment option, so as
* not to need to know now what name the statistics will have.
*/
We can change it to:
/*
* We make use of CreateTrigStmt's trigcomment option, so as
* not to need to know now what name the triggers will have.
*/
>
> 7. Whole-row reference restriction: implementation gap or deliberate?
>
> Triggers whose WHEN clause contains a whole-row reference (OLD.*,
> NEW.*) are rejected. Is this a deliberate decision, or a known gap
> left for a follow-up? If the latter, a XXX comment at the rejection
> site would help future contributors:
>
> /*
> * XXX: whole-row Vars could in principle be handled by passing the
> * target table's composite type OID as to_rowtype, but
> * generateClonedTriggerStmt() currently has no access to it.
> */
>
Other places already reject it (search found_whole_row in parse_utilcmd.c).
It cannot be supported because the source table's whole-row type
differs from the target table's whole-row type.
The v10 in https://www.postgresql.org/message-id/CACJufxEcKTa5DaDJS%3DZ25xezCEyuLbSzORDSmT4%3DjyZymsAK8A%40mail...
has addressed most of the issues mentioned above.
Only issue remaining is changing one of the comments to
/*
* We make use of CreateTrigStmt's trigcomment option, so as
* not to need to know now what name the triggers will have.
*/
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2026-06-04 02:10 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-01-16 07:34 Re: CREATE TABLE LIKE INCLUDING TRIGGERS jian he <[email protected]>
2026-01-16 12:13 ` Andrey Borodin <[email protected]>
2026-01-21 12:13 ` Zsolt Parragi <[email protected]>
2026-01-22 06:08 ` jian he <[email protected]>
2026-01-22 14:20 ` Zsolt Parragi <[email protected]>
2026-02-05 06:15 ` jian he <[email protected]>
2026-02-06 14:49 ` Zsolt Parragi <[email protected]>
2026-02-09 07:43 ` jian he <[email protected]>
2026-05-27 06:03 ` lakshmi <[email protected]>
2026-05-27 22:53 ` Zsolt Parragi <[email protected]>
2026-06-04 01:38 ` jian he <[email protected]>
2026-06-04 02:10 ` jian he <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox