Re: Partial table duplication via triggger

On 2024-02-22 15:14 +0100, Moreno Andreo wrote:
> suppose I have 2 tables
>
> CREATE TABLE t1(
>     id uuid,
>     name text,
>     surname text,
>     ...
>     PRIMARY KEY(id)
> )
>
> CREATE TABLE t2(
>     id uuid,
>     master_id uuid,
>     op_ts timestamp with time zone,
>     name text,
>     surname text,
>     ...
>     PRIMARY KEY(id)
> )
>
> I need to write an AFTER TRIGGER on UPDATE so all columns of t1 go in the
> same columns in t2 (except for t1.id that goes in t2.master_id, and t2.op_ts
> gets now())
>
> I cannot write an 1 to 1 column assignment (like NEW.name := OLD.name and so
> on) because the trigger has to be used on many tables, that has different
> (and evolving) schema and I don't want to write dozen of function that have
> to be frequently mantained.
>
> I'm quite noob at pl-pgsql; at the moment I wrote this, but I can't "tell
> the function that fields are from OLD row" (the error is "missing
> FROM-clause entry for table 'old')
>
> I tried also with field names alone (without OLD.), with no success.
> Trigger is fired AFTER UPDATE in t1 (CREATE TRIGGER update_id AFTER UPDATE
> ON t1 FOR EACH ROW EXECUTE PROCEDURE update_op());
>
> CREATE OR REPLACE FUNCTION update_op() RETURNS TRIGGER
> AS $$
> DECLARE
>     fieldlist text := (select string_agg(column_name, ', ')
>                         from information_schema.columns c
>                         where table_name = TG_TABLE_NAME and
>                               (column_name <> 'id'));
>
>     oldfieldlist text := (select string_agg(column_name, ', OLD.')
>                         from information_schema.columns c
>                         where table_name = TG_TABLE_NAME and
>                               (column_name <> 'id'));
>
> BEGIN
>         EXECUTE 'INSERT INTO t2 (master_id, op_ts, '|| fieldlist ||') VALUES
> (OLD.id, now(), OLD.'||oldfieldlist||')' USING OLD;
>         RETURN NULL;
> END;
> $$
> LANGUAGE 'plpgsql';
>
> What am I missing?

The parameters you pass in with USING have to be referenced as $1, $2,
and so on. For example:

DECLARE
fieldlist text := (
SELECT string_agg(quote_ident(column_name), ', ')
FROM information_schema.columns
WHERE table_name = TG_TABLE_NAME AND column_name <> 'id'
);
oldfieldlist text := (
SELECT string_agg('$1.' || quote_ident(column_name), ', ')
FROM information_schema.columns
WHERE table_name = TG_TABLE_NAME AND column_name <> 'id'
);
BEGIN
EXECUTE '
INSERT INTO t2 (id, master_id, op_ts, ' || fieldlist || ')
VALUES (gen_random_uuid(), $1.id, now(), ' || oldfieldlist || ')
' USING OLD;
RETURN NULL;
END;

Also make sure to use quote_ident() when constructing statements that
way to avoid SQL injections via column names in this case. Or use
format() with placeholder %I, although it's not simpler when you need to
construct that variable list of identifiers.

--
Erik