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?

I think I've figured it out: EXECUTE 'CREATE TABLE ' || _tblVar || ' ( foo integer NOT NULL, bar varchar NOT NULL)'; — Jmoney38
– 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 — Jack Douglas
– Jack Douglas, Commented Sep 6, 2011 at 20:50
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
– Frank Heikens, 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 — Jack Douglas
– Jack Douglas, Commented Sep 7, 2011 at 15:49
@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
– Frank Heikens, Commented Sep 7, 2011 at 16:09

Erwin Brandstetter · Accepted Answer · 2023-04-15 01:33:39Z

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:

filiprem · Accepted Answer · 2023-04-14 23:25:53Z

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

For a CREATE statement you cannot use the cast tabname::regclass in the example because, obviously, the table does not exist, yet. — Erwin Brandstetter
– Erwin Brandstetter, Commented Jun 21, 2012 at 13:18

Fayiz Fareed · Accepted Answer · 2020-04-03 05:47:32Z

-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

answered Apr 3, 2020 at 5:47

Fayiz Fareed

11 bronze badge

Sorry, no, $1 is not a parameter in PL/pgSQL.

Colin 't Hart
– Colin 't Hart

2020-04-03 07:41:34 +00:00
Commented Apr 3, 2020 at 7:41
1

then, may I know what is that

Fayiz Fareed
– Fayiz Fareed

2020-04-03 13:45:45 +00:00
Commented Apr 3, 2020 at 13:45

Add a comment |

Stack Exchange Network

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

3 Answers 3

Your Answer

Linked

Hot Network Questions

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

3 Answers 3

Your Answer

Sign up or log in

Post as a guest

Linked

Related

Hot Network Questions