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?

9
  • I think I've figured it out: EXECUTE 'CREATE TABLE ' || _tblVar || ' ( foo integer NOT NULL, bar varchar NOT NULL)';
    – Jmoney38
    Commented Sep 6, 2011 at 20:46
  • Thats right - execute ... using can only use substitutions where you could normally have bind variables - ie not for table names etc Commented Sep 6, 2011 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. Commented Sep 7, 2011 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 Commented Sep 7, 2011 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. Commented Sep 7, 2011 at 16:09

3 Answers 3

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:

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

1
  • 1
    For a CREATE statement you cannot use the cast tabname::regclass in the example because, obviously, the table does not exist, yet. Commented Jun 21, 2012 at 13:18
-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

2
  • Sorry, no, $1 is not a parameter in PL/pgSQL. Commented Apr 3, 2020 at 7:41
  • 1
    then, may I know what is that Commented Apr 3, 2020 at 13:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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