Re: CREATE TABLE LIKE INCLUDING TRIGGERS

On Thu, May 28, 2026 at 6:53 AM Zsolt Parragi <zsolt(dot)parragi(at)percona(dot)com> 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/