35

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?

Mr.
  • 9,429
  • 13
  • 58
  • 82
  • I have solution that do not apply http://stackoverflow.com/questions/10639963/postgres-dynamic-query-function but interesting for this issue : SELECT '"backup_' || TO_CHAR(CURRENT_DATE,'yyyy-mm-dd') || '"' AS tname; \gset CREATE TABLE :tname AS SELECT * FROM backup; – Roman Tkachuk Feb 22 '17 at 19:33

1 Answers1

62

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.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • 1
    Will the name automatically be quoted if it contains special characters? I'm not really a PostgreSQL user, but it seems doubtful that a simple FORMAT() function would be clever enough to do that. – Andriy M Nov 08 '12 at 15:54
  • 3
    @AndriyM The `%I` interpolation placeholder in the [`format()`](http://www.postgresql.org/docs/current/static/functions-string.html#FUNCTIONS-STRING-OTHER) function is specifically for postgres identifiers and will safely quote the supplied value. – dbenhur Nov 08 '12 at 19:27
  • @dbenhur: Oh, good, thanks. Nice solution then, Craig! (apparently :) – Andriy M Nov 08 '12 at 19:58