How to use variable as table name?

Question

BEGIN
   _table_name := 'mytable';

   CREATE TEMPORARY TABLE _table_name (
     id integer NOT NULL,
     name character varying,
     active boolean
   ) ON COMMIT DROP';

-- logic here

RETURN QUERY SELECT table1.id, table1.name FROM _table_name AS table1;
END;

I have simplified my problem. I'm trying to use a variable as the table name.

I know you can do things like SELECT * FROM table1 WHERE id = _id where _id is a declared varaible.

I know I can do this EXECUTE, but then I have a query like: INSERT INTO table2 (id) SELECT id FROM unnest(_ids) as id where _ids is an array.

Anyway to solve problem 1 with using a variable as table name? and problem 2, using unnest inside EXECUTE?

So the problem is that the queries take _table_name as a literal table name, it doesn't look like it's using 'mytable' as the table name instead.

http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN — , Mar 09 '15 at 22:00
@a_horse_with_no_name I already read that. My argument was that how come you can use variables in literal queries like in the example with `_id` vs the create table example. — user962449, Mar 09 '15 at 22:03

Gareth Flowers · Accepted Answer · 2015-03-10T00:14:10.863

If you're dynamically changing the table name (i.e. via a variable) then you will need to use EXECUTE. You can use this with arrays and unnest, as long as you cast the arrays to/from a TEXT representation.

DECLARE 
    _ids INT[] = ARRAY[ 1, 2, 3, 4, 5 ];
    _table_name TEXT = 'mytable';
BEGIN 
  EXECUTE
    'INSERT INTO ' || QUOTE_IDENT( _table_name ) || ' (id)
     SELECT id
     FROM unnest( ' || QUOTE_LITERAL( _ids::TEXT ) || '::INT[] ) AS id';
END;

score 1 · Answer 2 · edited May 23 '17 at 12:15

You have been told about dynamic SQL with EXECUTE in plpgsql. You build the query string dynamically including invariable code and identifiers.

But do not concatenate values. Use the USING clause instead:

DECLARE 
   _ids INT[] = ARRAY[ 1, 2, 3, 4, 5 ];
   _table_name TEXT = 'mytable';
BEGIN 
   EXECUTE
    'INSERT INTO ' || quote_ident(_table_name) || ' (id)
     SELECT * FROM unnest($1)'
   USING ids;
END;

Avoids error-prone casting back and forth.

Table name as a PostgreSQL function parameter

How to use variable as table name?

2 Answers2