CODEWITHSUNDEEP
sqlpostgresqlvariablesplpgsqldynamic-sql
Bushwacka
Bushwacka

Reputation: 915

Table variable in PostgreSQL without execute command

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$;

Upvotes: 0

Views: 209

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656331

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:

Upvotes: 1

Related Questions