From: | Moreno Andreo <moreno(dot)andreo(at)evolu-s(dot)it> |
---|---|
To: | Erik Wienhold <ewie(at)ewie(dot)name> |
Cc: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: [SPAM] Re: Partial table duplication via triggger |
Date: | 2024-02-22 17:25:42 |
Message-ID: | 9799c949-84c9-4a12-93ed-ef5c77a745b7@evolu-s.it |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Greg Sabino Mullane | 2024-02-22 18:33:00 | Re: Partitioning, Identity and Uniqueness (given pg 16 changes) |
Previous Message | Adrian Klaver | 2024-02-22 17:17:18 | Re: pg_dump performance issues |