Partial table duplication via triggger

From: Moreno Andreo <moreno(dot)andreo(at)evolu-s(dot)it>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Partial table duplication via triggger
Date: 2024-02-22 14:14:53
Message-ID: c521dbf2-e1f0-4aa3-9d83-1b989bb8ac00@evolu-s.it
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi *,
    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?
Thanks,
Moreno

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Dominique Devienne 2024-02-22 14:40:23 Re: pg_dump performance issues
Previous Message Sasmit Utkarsh 2024-02-22 14:05:14 Re: Postgresql assistance needed