Postgres plpgsql - Using a variable inside of a dynamic create statement

Question

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?

Answer 1

In addition to what @filiprem wrote, here is how you do this properly:

...
DECLARE
   tblVar text := 'myTable';
BEGIN
EXECUTE '
CREATE TABLE ' || quote_ident(tblVar) ||'( 
   foo integer NOT NULL, 
   bar text NOT NULL)';
...

Use quote_ident() to avoid SQL injection or syntax errors. It will quote names with non-standard characters or reserved words.

I also replaced the double-quotes you had around the string value in your example with single-quotes.

Answer 2

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