current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 4 down vote favorite
1

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?

share|improve this question
    
I think I've figured it out: EXECUTE 'CREATE TABLE ' || _tblVar || ' ( foo integer NOT NULL, bar varchar NOT NULL)'; –  Jmoney38 Sep 6 '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 6 '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 7 '11 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♦ Sep 7 '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 7 '11 at 16:09

2 Answers 2

up vote 4 down vote

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.

share|improve this answer
up vote 2 down vote

See http://www.postgresql.org/docs/9.1/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

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;

In other words:

Yes, there is such limitation. You cannot use parameters for table/column names - that's because Postgresql needs to be able to parse query on compiling the dynamic SQL statement. Parser must be able to identify used relations.

Side note: probably this limitation applies to dynamic SQL in other DBMS, including Oracle: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/dynamic.htm#CHDHGHIF

share|improve this answer
1  
For a CREATE statement you cannot use the cast tabname::regclass in the example because, obviously, the table does not exist, yet. –  Erwin Brandstetter Jun 21 '12 at 13:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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