up vote 1 down vote favorite
1
share [fb]

Using Postgres pl/pgsql, I'm attempting to create a table using a dynamic EXECUTE command, such as:

 ...
 DECLARE
    tblVar varchar := "myTable";
 BEGIN
 EXECUTE 'CREATE TABLE $1 ( 
             foo integer NOT NULL, 
             bar varchar NOT NULL)'
 USING _tblVar;
 ...

However, I continue to receive the error message

ERROR: syntax error at or near "$1"

If I don't use the $1 token and, instead, write the string myTable it works just fine.

Is there a limitation on using dynamic statements for CREATE calls?

link|improve this question
I think I've figured it out: EXECUTE 'CREATE TABLE ' || _tblVar || ' ( foo integer NOT NULL, bar varchar NOT NULL)'; – Jmoney38 Sep 6 '11 at 20:46
Thats right - execute ... using can only use substitutions where you could normally have bind variables - ie not for table names etc – Jack Douglas♦ Sep 6 '11 at 20:50
2  
Use quote_ident() to avoid SQL injection and other problems with dynamic object names. You might need lower() as well, to create only lower case objects. – Frank Heikens Sep 7 '11 at 5:56
@Frank only if the table name is coming from an untrusted source, and then IMO he should do more than quote_ident - such as restrict to ~'^[a-z]{3,10}$' and add a prefix – Jack Douglas♦ Sep 7 '11 at 15:49
1  
@Jack: It's a variable so you have to protect your database against major problems. The example already shows issues with casing, myTable is going to be mytable in lower case. quote_ident works fine, no restrictions needed. A maximum length might be handy, 63 characters is the max. – Frank Heikens Sep 7 '11 at 16:09
show 2 more comments
feedback

1 Answer

up vote 1 down vote

See http://www.postgresql.org/docs/9.1/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

Note that parameter symbols can only be used for data values — if you want to use dynamically determined table or column names, you must insert them into the command string textually. For example, if the preceding query needed to be done against a dynamically selected table, you could do this:

EXECUTE 'SELECT count(*) FROM '
    || tabname::regclass
    || ' WHERE inserted_by = $1 AND inserted <= $2'
   INTO c
   USING checked_user, checked_date;

In other words:

Yes, there is such limitation. You cannot use parameters for table/column names - that's because Postgresql needs to be able to parse query on compiling the dynamic SQL statement. Parser must be able to identify used relations.

Side note: probably this limitation applies to dynamic SQL in other DBMS, including Oracle: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/dynamic.htm#CHDHGHIF

link|improve this answer
feedback

Your Answer

 
or
required, but never shown

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