Table variable in PostgreSQL without execute command

Question

I would like to use variable for table name in my sql script below. Is possible to do that in different way than i show you below ? I mean without EXECUTE command? My script select data from one table and move to the another one. I want to avoid escaping quotes in my scripts.

DO $proc$
DECLARE
  v_table_name VARCHAR(100) := 'selected_customers';
BEGIN

EXECUTE 'INSERT INTO ' || v_table_name || '(name, surname, address)
                 SELECT name, surname, address FROM customers
                 WHERE name ILIKE ''mon%''';

END;
$proc$;

No. SQL statements require explicitly defined table names in the `from`/`into`/`update` clauses. To use variable names, you need dynamic SQL. — Gordon Linoff, Aug 03 '14 at 12:56

score 1 · Accepted Answer · edited May 23 '17 at 12:04

SQL strictly segregates code and data. To convert variables into code, you need dynamic SQL. The only other way would be to use at least two round trips to the server and concatenate code in your client, but there is really no point in doing that.

Whenever you turn data into code, be wary of possible SQL injection.

"To avoid escaping quotes", there are a number of possibilities. format() (Postgres 9.1+) can take care of identifiers and literals for you.

And use dollar-quoting to make your life easier - just like you do already for the body of the DO statement. Your example:

DO
$proc$
DECLARE
   v_table_name text := 'selected_customers';
BEGIN

EXECUTE format($$
   INSERT INTO %I
           (name, surname, address)
   SELECT name, surname, address
   FROM   customers
   WHERE  name ILIKE 'mon%'$$
  ,v_table_name text);

END
$proc$;

There are more options:

Define table and column names as arguments in a plpgsql function?

Table variable in PostgreSQL without execute command

1 Answers1