PostgreSQL - dynamic value as table name [duplicate]

Question

Possible Duplicate:
Postgres Dynamic Query Function

I wish to use the returned string from the query below as a table name for other query.

SELECT 'backup_' || TO_CHAR(CURRENT_DATE,'yyyy-mm-dd') 

as you can see it returns a string. I wish to use it as an input for another query, e.g.

CREATE TABLE (SELECT 'backup_' || TO_CHAR(CURRENT_DATE,'yyyy-mm-dd'))  AS * SELECT FROM backup 

Can it be done? Any clue how?

Answer 1

You will need to use the PL/PgSQL EXECUTE statement, via a DO block or PL/PgSQL function (CREATE OR REPLACE FUNCTION ... LANGUAGE plpgsql). Dynamic SQL is not supported in the ordinary SQL dialect used by PostgreSQL, only in the procedural PL/PgSQL variant.

DO $$ BEGIN EXECUTE format('CREATE TABLE %I AS SELECT * FROM backup', 'backup_' || to_char(CURRENT_DATE,'yyyy-mm-dd')); END; $$ LANGUAGE plpgsql; 

The format(...) function's %I and %L format-specifiers do proper identifier and literal quoting, respectively.

For literals I recommend using EXECUTE ... USING rather than format(...) with %L, but for identifiers like table/column names the format %I pattern is a nice concise alternative to verbose quote_ident calls.