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
  • 175,982
  • 27
  • 439
  • 600
Jmoney38
  • 1,155
  • 5
  • 13
  • 22
  • I think I've figured it out: 
    EXECUTE 'CREATE TABLE ' || _tblVar || ' ( 
         foo integer NOT NULL, 
         bar varchar NOT NULL)';
    – Jmoney38 Sep 06 '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 06 '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 07 '11 at 05: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 07 '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 07 '11 at 16:09
  • @Frank I'd be worried about postgres bugs being exploited with special/unicode characters, buffer overflows etc, wouldn't you? – Jack Douglas Sep 07 '11 at 16:20
  • I'm more worried about the dynamic creation of tables to begin with: You need extra permissions for this. And permissions should be as limited as possible. quote_ident() works fine and it's safe, that's why this function exists in the first place. – Frank Heikens Sep 07 '11 at 16:31
  • 63 utf-8 otctets is the name length limit, not 63 characters. (in postgresql characters are unicode code points), the type "name" already has the apropriate constraint. – Jasen Apr 14 '22 at 00:32
  • @FrankHeikens there is no indication of sql injection in the example code '$1' is for the the USING clause, it's not a reference to function arguments. – Jasen Apr 14 '22 at 00:59

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
  • 175,982
  • 27
  • 439
  • 600
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,539
  • 1
  • 18
  • 31
-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