CODEWITHSUNDEEP
sqlpostgresqlstored-proceduresplpgsqldynamic-sql
user962449
user962449

Reputation: 3873

How to use variable as table name?

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.

Upvotes: 1

Views: 277

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656724

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.

Upvotes: 1

user1322772
user1322772

Reputation:

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;

Upvotes: 2

Related Questions