pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Partial table duplication via triggger
3+ messages / 2 participants
[nested] [flat]

* Partial table duplication via triggger
@ 2024-02-22 14:14  Moreno Andreo <moreno.andreo@evolu-s.it>
  0 siblings, 1 reply; 3+ messages in thread

From: Moreno Andreo @ 2024-02-22 14:14 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

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

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Partial table duplication via triggger
@ 2024-02-22 16:49  Erik Wienhold <ewie@ewie.name>
  parent: Moreno Andreo <moreno.andreo@evolu-s.it>
  0 siblings, 1 reply; 3+ messages in thread

From: Erik Wienhold @ 2024-02-22 16:49 UTC (permalink / raw)
  To: Moreno Andreo <moreno.andreo@evolu-s.it>; +Cc: pgsql-general@lists.postgresql.org

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





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: [SPAM] Re: Partial table duplication via triggger
@ 2024-02-22 17:25  Moreno Andreo <moreno.andreo@evolu-s.it>
  parent: Erik Wienhold <ewie@ewie.name>
  0 siblings, 0 replies; 3+ messages in thread

From: Moreno Andreo @ 2024-02-22 17:25 UTC (permalink / raw)
  To: Erik Wienhold <ewie@ewie.name>; +Cc: pgsql-general@lists.postgresql.org



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.







^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2024-02-22 17:25 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-02-22 14:14 Partial table duplication via triggger Moreno Andreo <moreno.andreo@evolu-s.it>
2024-02-22 16:49 ` Erik Wienhold <ewie@ewie.name>
2024-02-22 17:25   ` Moreno Andreo <moreno.andreo@evolu-s.it>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox