current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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$;
share|improve this question
2  
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 3 '14 at 12:56

1 Answer 1

up vote 1 down vote accepted

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:

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.