I have multiple tables that have the same column names, they only vary in their column values, like:
tbl_log_a tbl_log_b tbl_log_c ...
26 tables from a to z. Each table has a trigger that calls a trigger function which does the exact same thing:
SELECT columnname FROM tbl_log_a
Other than that, all my trigger functions do the exact same thing. They differ in that they:
select columnname FROM tbl_log_a select columnname FROM tbl_log_b select columnname FROM tbl_log_c ...
So I have to create 26 trigger functions, one for each
tbl_log_%letter%. Is there a way to tell the trigger function to:
SELECT columnname FROM %currenttable%
%currenttable% I mean the table where the trigger is placed. Or:
SELECT columnname FROM tbl_log_%letter%
Is it possible in Postgres 9.1? I'm reading about dynamically determined tables. Any clue? I would like to store the table name itself inside a variable, not the columns inside that table, because the trigger function works on multiple columns inside that table.
I suggested that you use trigger arguments, but it's actually not necessary. You can use the automatic variables
TG_TABLE_NAME, or use
TG_RELID. These, alongside
EXECUTE for dynamic SQL, let you do what you want:
BEGIN EXECUTE format('SELECT colname FROM %I', TG_RELID) END;
BEGIN EXECUTE format('SELECT colname FROM %I.%I', TG_TABLE_SCHEMA, TG_TABLE_NAME) END;
(Of course these won't work as-is, since the
SELECT has no destination for the data. You have to use
EXECUTE format(..) INTO ... to store the result into a
DECLAREd variable), e.g.
DECLARE _colvar integer; BEGIN EXECUTE format('SELECT colname FROM %I.%I', TG_TABLE_SCHEMA, TG_TABLE_NAME) INTO _colvar; RAISE NOTICE 'colname value was %',_colvar; END;
External links referenced by this document: