Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rd9qm-0049Y3-Cf for pgsql-general@arkaria.postgresql.org; Thu, 22 Feb 2024 14:15:01 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rd9qj-00Bssv-Lf for pgsql-general@arkaria.postgresql.org; Thu, 22 Feb 2024 14:14:58 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rd9qj-00Bssn-8i for pgsql-general@lists.postgresql.org; Thu, 22 Feb 2024 14:14:57 +0000 Received: from mail.evolu-s.it ([77.81.232.174]) by magus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rd9qg-000LjS-Oa for pgsql-general@lists.postgresql.org; Thu, 22 Feb 2024 14:14:57 +0000 dkim-signature: v=1; a=rsa-sha256; d=evolu-s.it; s=dkim; c=relaxed/relaxed; q=dns/txt; h=From:Subject:Date:Message-ID:To:MIME-Version:Content-Type; bh=F5cUNigmuIPPthPdCHNCmMxM1vnv106nOIWpptsyNeg=; b=Gjnm3fLw2APTXhlkDMHfZF+kq13ZcL/hCr2oYtTOMg7Ql5ewyoPCVFK1Hewiw4u0f1m9urXX1V2h0KIS5+nzrAJBhknXAODiHs55wEoF20x4HH6UYhF4bkAidzGTM4KB0hMrPwywVaYRQ8TxGaE9EtxBuzKdRjLr7fMs4zOnUcg= Received: from [192.168.1.101] (res-129401d.ppp.twt.it [83.217.179.105]) by mail.evolu-s.it with ESMTPSA (version=TLSv1.2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128) ; Thu, 22 Feb 2024 15:14:53 +0100 Content-Type: multipart/alternative; boundary="------------0tdZpc0kKwARsb3u2IW86YJH" Message-ID: Date: Thu, 22 Feb 2024 15:14:53 +0100 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Content-Language: en-US To: pgsql-general@lists.postgresql.org From: Moreno Andreo Subject: Partial table duplication via triggger List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------0tdZpc0kKwARsb3u2IW86YJH Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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 --------------0tdZpc0kKwARsb3u2IW86YJH Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
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
--------------0tdZpc0kKwARsb3u2IW86YJH--