PostgreSQL Trigger Function: Selecting from Current Table

dynamic-sqlfunctionsplpgsqlpostgresqltrigger

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%

By %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.

TG_TABLE_NAME
TG_TABLE_SCHEMA

Best Answer

I suggested that you use trigger arguments, but it's actually not necessary. You can use the automatic variables TG_TABLE_SCHEMA and 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;

or

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;
Related Question