8

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 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 says try topanswers.xyz 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 says try topanswers.xyz 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
5

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.

| improve this answer | |
3

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;

As noted in comments below, the cast method is not always feasible, especially for CREATE statements. Consider format function, for example:

EXECUTE format(
  'CREATE TABLE %I (%I %I, %I %I)',
  v_tabname,
  v_col1name, v_col1type,
  v_col2name, v_col2type);

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

| 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
-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

| improve this answer | |
  • Sorry, no, $1 is not a parameter in PL/pgSQL. – Colin 't Hart Apr 3 at 7:41
  • then, may I know what is that – Mehdi Apr 3 at 13:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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