Postgresql – Postgres plpgsql – Using a variable inside of a dynamic create statement

dynamic-sqlplpgsqlpostgresql

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?

Best Answer

In addition to what @filiprem wrote, here is how you do this 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() 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.

Related Question