PostgreSQL - dynamic value as table name
dynamic-sql, postgresql, postgresql-9.1, sql
Solution
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.
Problem
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?