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.
