Обсуждение: 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.