PostgreSQL trigger function that selects from %current% table

Question

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

Craig Ringer · Accepted Answer · 2015-08-26T02:27:52.907

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;

score 3 · Answer 2 · answered Aug 26 '15 at 14:56

The actual syntax corresponding to the imaginary SELECT columnname FROM %currenttable% would be, in plpgsql:

execute format('SELECT columnname FROM %I.%I',
                TG_TABLE_SCHEMA, TG_TABLE_NAME);

The TG_* built-in variables are documented in Trigger Procedures and the execute and format plpgsql constructs in Basic Statements.

The query above is absurd by itself (it selects results that go nowhere); the intent is to just show the base syntax on which an actual query could be built.

score 3 · Answer 3 · edited May 23 '17 at 12:40

Or you can use TG_RELID, but since its data type is plain oid, not regclass, one must cast it to regclass explicitly to get the auto-conversion to a schema-qualified (only if the current search_path requires it), cleanly escaped table name. The documentation:

TG_RELID

Data type oid; the object ID of the table that caused the trigger invocation.

Bold emphasis mine. I wonder why they did not make it regclass to begin with ...

EXECUTE format('SELECT columnname FROM %s', TG_RELID::regclass);

And it's still unclear what you are doing with the result. Typically, you would use that in an INSERT / UPDATE / DELETE statement or write the result to a variable:

EXECUTE format('SELECT columnname FROM %s', TG_RELID::regclass)
INTO my_variable;

Only the first value is assigned. If SELECT finds more rows, the rest is discarded. You might want to add ORDER BY ... LIMIT 1.

PostgreSQL trigger function that selects from %current% table

3 Answers3

Linked