14

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?

Erwin Brandstetter
  • 185,527
  • 28
  • 463
  • 633
Jmoney38
  • 1,175
  • 5
  • 13
  • 22

3 Answers3

13

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

...
DECLARE
   tbl_var text := 'myTable';   -- I would not use mixed case names ..
BEGIN
   EXECUTE '
   CREATE TABLE ' || quote_ident(tbl_var) || '( 
     foo integer NOT NULL, 
   , bar text NOT NULL)';
...

Use quote_ident() (or format()) to defend against SQL injection and syntax errors. It double-quotes identifiers 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.

See:

Erwin Brandstetter
  • 185,527
  • 28
  • 463
  • 633
8

Yes, there is such limitation.

EXECUTE 'SELECT aColumn FROM $1' USING tableVar;

will not work. You cannot use parameters for table/column names - that's because PostgerSQL query parser must identify all used tables in order to compile the query.

Quote from PL/pgSQL docs about dynamic SQL commands:

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;

As noted in comments below, the cast method is not always feasible, especially for CREATE statements. Better use the format(formatstr, *formatarg) function:

EXECUTE format(
  'CREATE TABLE %I (%I %I, %I %I)',
  v_tabname,
  v_col1name, v_col1type,
  v_col2name, v_col2type);

Side note: this limitation comes from SQL language itself. Parser needs to know table/column names to resolve if query text is correct or not. Hence it applies to dynamic SQL in other DBMS-es, Oracle for example: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/dynamic.htm#CHDHGHIF

filiprem
  • 6,747
  • 1
  • 19
  • 32
-1

In the code above, you are using $1 which is an argument which you got as a parameter from the function. In this case, you can't use $1 inside the string. So, the code should be like this

EXECUTE 'CREATE TABLE ' || $1 || ' ( 
         foo integer NOT NULL, 
         bar varchar NOT NULL)'

In here || this mark indicates that joining a string like + sign

Fayiz Fareed
  • 1
  • 1