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?

• Jmoney38: I think I've figured it out: EXECUTE 'CREATE TABLE ' || _tblVar || ' ( foo integer NOT NULL, bar varchar NOT NULL)'
• Jack Douglas: Thats right - execute ... using can only use substitutions where you could normally have bind variables - ie not for table names etc
• Frank Heikens: 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.
• Jack Douglas: @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
• Frank Heikens: @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.

Answer 1

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:

• SQL injection in Postgres functions vs prepared queries
• Insert text with single quotes in PostgreSQL

Answer 2

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

• Erwin Brandstetter: For a CREATE statement you cannot use the cast tabname::regclass in the example because, obviously, the table does not exist, yet.

Answer 3

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

• Colin 't Hart: Sorry, no, 1ドル is not a parameter in PL/pgSQL.
• Fayiz Fareed: then, may I know what is that