How to Dynamically Use Tg_Table_Name in Postgresql 8.2

How to dynamically use TG_TABLE_NAME in PostgreSQL 8.2?

I was looking for the exact same thing a couple of years back. One trigger function to rule them all! I asked on usenet lists, tried various approaches, to no avail. The consensus on the matter was this could not be done. A shortcoming of PostgreSQL 8.3 or older.

Since PostgreSQL 8.4 you can just:

EXECUTE 'INSERT INTO ' || TG_RELID::regclass::text || ' SELECT ($1).*'
USING NEW;

With pg 8.2 you have a problem:

cannot dynamically access columns of NEW / OLD. You need to know
column names at the time of writing the trigger function.
NEW / OLD are not visible inside EXECUTE.
EXECUTE .. USING not born yet.

There is a trick, however.

Every table name in the system can serve as composite type of the same name. Therefore you can create a function that takes NEW / OLD as parameter and execute that. You can dynamically create and destroy that function on every trigger event:

Trigger function:

CREATE OR REPLACE FUNCTION trg_cdc()
  RETURNS trigger AS
$func$
DECLARE
   op      text := TG_OP || '_' || TG_WHEN;
   tbl     text := quote_ident(TG_TABLE_SCHEMA) || '.'
                || quote_ident(TG_TABLE_NAME);
   cdc_tbl text := quote_ident(TG_TABLE_SCHEMA) || '.'
                || quote_ident('cdc_' || TG_TABLE_NAME);
BEGIN

EXECUTE 'CREATE FUNCTION f_cdc(n ' || tbl || ', op text)
  RETURNS void AS $x$ BEGIN
  INSERT INTO ' || cdc_tbl || ' SELECT op, (n).*;
END $x$ LANGUAGE plpgsql';

CASE TG_OP
WHEN 'INSERT', 'UPDATE' THEN
   PERFORM f_cdc(NEW, op);
WHEN 'DELETE' THEN
   PERFORM f_cdc(OLD, op);
ELSE
   RAISE EXCEPTION 'Unknown TG_OP: "%". Should not occur!', TG_OP;
END CASE;

EXECUTE 'DROP FUNCTION f_cdc(' || tbl || ', text)';

IF TG_OP = 'DELETE' THEN
    RETURN OLD;
ELSE
    RETURN NEW;
END IF;

END
$func$  LANGUAGE plpgsql;

Trigger:

CREATE TRIGGER cdc
BEFORE INSERT OR UPDATE OR DELETE ON my_tbl
FOR EACH ROW EXECUTE PROCEDURE trg_cdc();

Table names have to be treated like user input. Use quote_ident() to defend against SQL injection.

However, this way you create and drop a function for every single trigger event. Quite an overhead, I would not go for that. You will have to vacuum some catalog tables a lot.

Middle ground

PostgreSQL supports function overloading. Therefore, one function per table of the same base name (but different parameter type) can coexist. You could take the middle ground and dramatically reduce the noise by creating f_cdc(..) once per table at the same time you create the trigger. That's one tiny function per table. You have to observe changes of table definitions, but tables shouldn't change that often. Remove CREATE and DROP FUNCTION from the trigger function, arriving at a small, fast and elegant trigger.

I could see myself doing that in pg 8.2. Except that I cannot see myself doing anything in pg 8.2 anymore. It has reached end of life in December 2011. Maybe you can upgrade somehow after all.

INSERT with dynamic table name in trigger function

Modern PostgreSQL

format() has a built-in way to escape identifiers. Simpler than before:

CREATE OR REPLACE FUNCTION foo_before()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE format('INSERT INTO %I.%I SELECT $1.*'
                , TG_TABLE_SCHEMA, TG_TABLE_NAME || 'shadow')
   USING OLD;

   RETURN OLD;
END
$func$;

Works with a VALUES expression as well.

db<>fiddle here

_{Old sqlfiddle}

Major points

Use format() or quote_ident() to quote identifiers (automatically and only where necessary), thereby defending against SQL injection and simple syntax violations.

This is necessary, even with your own table names!
Schema-qualify the table name. Depending on the current search_path setting a bare table name might otherwise resolve to another table of the same name in a different schema.
Use EXECUTE for dynamic DDL statements.
Pass values safely with the USING clause.
Consult the fine manual on Executing Dynamic Commands in plpgsql.
Note thatRETURN OLD; in the trigger function is required for a trigger BEFORE DELETE. Details in the manual.

You get the error message in your almost successful version because OLD is not visible inside EXECUTE. And if you want to concatenate individual values of the decomposed row like you tried, you have to prepare the text representation of every single column with quote_literal() to guarantee valid syntax. You would also have to know column names beforehand to handle them or query the system catalogs - which stands against your idea of having a simple, dynamic trigger function ...

My solution avoids all these complications. Also simplified a bit.

PostgreSQL 9.0 or earlier

format() is not available, yet, so:

CREATE OR REPLACE FUNCTION foo_before()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
    EXECUTE 'INSERT INTO ' || quote_ident(TG_TABLE_SCHEMA)
                    || '.' || quote_ident(TG_TABLE_NAME || 'shadow')
                    || ' SELECT $1.*'
    USING OLD;

    RETURN OLD;
END
$func$;

How to dynamically use TG_TABLE_NAME in PostgreSQL 8.2?

How to pass NEW.* to EXECUTE in trigger function

Best with the USING clause of EXECUTE:

CREATE FUNCTION foo ()
  RETURNS trigger AS
$func$
BEGIN
  IF TG_OP = 'INSERT' THEN
     EXECUTE format('INSERT INTO %s SELECT $1.*'
                  , 'samples_' || left(NEW.md5, 2);
     USING NEW;
  END IF;
  RETURN NULL;
END
$func$ LANGUAGE plpgsql;

And EXECUTE does not require parentheses.

And you are aware that identifiers are folded to lower case unless quoted where necessary (%I instead of %s in format()).

More details:

INSERT with dynamic table name in trigger function
How to dynamically use TG_TABLE_NAME in PostgreSQL 8.2?

Inserting NEW.* from a generic trigger using EXECUTE in PL/pgsql

I've managed to get this to work by dynamically compiling a function that accepts the NEW row as a parameter:

    EXECUTE 'create or replace function partition_insert(r ' || TG_TABLE_NAME || ') RETURNS void AS $FUNC$' || 
            'BEGIN ' ||
                'insert into ' || TG_TABLE_NAME || ' SELECT r.*; ' ||
            'END $FUNC$ LANGUAGE plpgsql VOLATILE';
    PERFORM partition_insert(NEW);

As Postgres functions are polymorphic, this will generate a different function for each table that uses this trigger.

Despite being an ugly kludge, this seems to do the job.

Although it looks like I could define each polymorphic variation up front when I build the system, because of caching, I must recompile the function whenever I create or drop a child table so that the function uses the latest insert RULE.

EDIT: Additional wrinkles
There's a little gotcha with this technique: If this EXECUTE/PERFORM action is rolled-back on the first attempt due to another error (for example, in my case a CHECK constraint failure) then the function containing this code seems to cache a reference to the rolled-back partition_insert() function it created using the EXECUTE and subsequent calls fail due to a cached object not being found.

I resolved this by pre-creating stub versions of the function for each required table-type parameter when I define the database.

Is it possible to dynamically loop through a table's columns?

Take a look at the information_schema, there is a view "columns". Execute a query to get all current columnnames from the table that fired the trigger:

SELECT 
    column_name 
FROM 
    information_schema.columns 
WHERE 
    table_schema = TG_TABLE_SCHEMA 
AND 
    table_name = TG_TABLE_NAME;

Loop through the result and there you go!

More information can be found in the fine manual.

Self-managing PostgreSQL partition tables

You are mixing double precision output of date_part() with text '-'. That doesn't make sense to PostgreSQL. You would need an explicit cast to text. But there is a much simpler way to do all of this:

startdate:=date_part('year',to_timestamp(NEW.date))
||'-'||date_part('month',to_timestamp(NEW.date))
||'-'||date_part('day',to_timestamp(NEW.date));

Use instead:

startdate := to_char(NEW.date, 'YYYY-MM-DD');

This makes no sense either:

EXECUTE 'CREATE TABLE $1 (
        CHECK (date >= DATE $2 AND date < DATE $3 )
    ) INHERITS (pings)' USING quote_ident(tablename),startdate,enddate;

You can only supply values with the USING clause. Read the manual here. Try instead:

EXECUTE 'CREATE TABLE ' || quote_ident(tablename) || ' (
            CHECK ("date" >= ''' || startdate || ''' AND
                   "date" <  ''' || enddate   || '''))
            INHERITS (ping)';

Or better yet, use format(). See below.

Also, like @a_horse answered: You need to put your text values in single quotes.

Similar here:

EXECUTE 'INSERT INTO $1 VALUES (NEW.*)' USING quote_ident(tablename);

Instead:

EXECUTE 'INSERT INTO ' || quote_ident(tablename) || ' VALUES ($1.*)'
USING NEW;

Complete working demo

CREATE TABLE ping (ping_id integer, the_date date);

CREATE OR REPLACE FUNCTION trg_ping_partition()
  RETURNS trigger AS
$func$
DECLARE
   _tbl text := to_char(NEW.the_date, '"ping_"YYYY_DDD_') || NEW.ping_id;
BEGIN
   IF NOT EXISTS (
      SELECT 1
      FROM   pg_catalog.pg_class c
      JOIN   pg_catalog.pg_namespace n ON n.oid = c.relnamespace
      WHERE  n.nspname = 'public'  -- your schema
      AND    c.relname = _tbl
      AND    c.relkind = 'r') THEN

      EXECUTE format('CREATE TABLE %I (CHECK (the_date >= %L AND
                                              the_date <  %L)) INHERITS (ping)'
              , _tbl
              , to_char(NEW.the_date,     'YYYY-MM-DD')
              , to_char(NEW.the_date + 1, 'YYYY-MM-DD')
              );
   END IF;

   EXECUTE 'INSERT INTO ' || quote_ident(_tbl) || ' VALUES ($1.*)'
   USING NEW; 

   RETURN NULL;
END
$func$ LANGUAGE plpgsql SET search_path = public;

CREATE TRIGGER insbef
BEFORE INSERT ON ping
FOR EACH ROW EXECUTE PROCEDURE trg_ping_partition();

Update: Later versions of Postgres have more elegant ways to check if a table exists:
- How to check if a table exists in a given schema
to_char() can take a date as $1. That's converted to timestamp automatically.

The manual on date / time functions.
(Optionally) SET the search_path for the scope of your function to avoid misconduct with a changed search_path setting.
Multiple other simplifications and improvements. Compare the code.

Tests:

INSERT INTO ping VALUES (1, now()::date);
INSERT INTO ping VALUES (2, now()::date);
INSERT INTO ping VALUES (2, now()::date + 1);
INSERT INTO ping VALUES (2, now()::date + 1);

SQL Fiddle.

How to Dynamically Use Tg_Table_Name in Postgresql 8.2