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

On 22/02/24 17:49, Erik Wienhold wrote:

On 2024-02-22 15:14 +0100, Moreno Andreo wrote:

suppose I have 2 tables
[snip]
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,
It worked perfectly!
I had not clear in mind how to use $1, $2, etc, with using; after your
reply I had a closer look at the docs and now it's clearer to me.

Many thanks,
Moreno.