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

Answer 1

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;

Answer 2

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.

Answer 3

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.

Related:

• SQL injection in Postgres functions vs prepared queries
• How to dynamically use TG_TABLE_NAME in PostgreSQL 8.2?
• Table name as a PostgreSQL function parameter