How to INSERT into a table whose name is stored as variable inside the same script?

Question

To brief the question, I have a table named partitions_info which maintains the details of all other tables I am using.

Columns of partitions_info are tablename, starttime, endtime

I have compiled following trigger for selecting a particular table name from partitions_info table (schema shown above) based on some conditions. I store the table name into a variable table_name. Now how can I insert some values into the table which is stored in variable table_name?

CREATE OR REPLACE FUNCTION emp_fun()
RETURNS TRIGGER AS
$$
DECLARE
    table_name varchar(65);
BEGIN
    SELECT tablename FROM partitions_info INTO table_name WHERE starttime<'2015-12-04 14:23:56' AND endtime>'2015-12-04 14:23:56';
    IF table_name IS NULL THEN
        INSERT INTO default_table VALUES (NEW.*);
    END IF;
    # Here I wanted to insert into table name which is stored in variable whose name is stored in "table_name" variable declared above
    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

You need dynamic SQL: http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN — , Dec 08 '15 at 10:57

score 1 · Accepted Answer · edited May 23 '17 at 11:45

The implementation of the dynamic INSERT can look like this (including some other minor improvements):

CREATE OR REPLACE FUNCTION emp_fun()
  RETURNS TRIGGER AS
$func$
DECLARE
   table_name text;
BEGIN
   SELECT INTO table_name p.tablename 
   FROM   partitions_info p
   WHERE  p.starttime < '2015-12-04 14:23:56'
   AND    p.endtime   > '2015-12-04 14:23:56';

   IF FOUND THEN  -- simpler, cheaper, safer
     EXECUTE 'INSERT INTO ' || quote_ident(table_name) || ' VALUES ($1.*)'
     USING NEW;
   ELSE
      INSERT INTO default_table VALUES (NEW.*);
   END IF;

   RETURN NEW;
END
$func$  LANGUAGE plpgsql;

How to INSERT into a table whose name is stored as variable inside the same script?

1 Answers1