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 |