Search code examples
postgresqlsubquerydynamic-sqlidentifier

How to use a subquery as a database name in a DDL command?

I am wondering if it's possible to use the result of a subquery as database name in a PostgreSQL (9.5.1) DDL statement.

For example, I wanted to alter the current database with something like:

ALTER DATABASE (SELECT current_database()) SET a_var TO 'a_value';

If I run this, an error occurs:

ERROR:  syntax error at or near "("
LINE 1: ALTER DATABASE (SELECT current_database()) SET ...

What's the correct way to use the sub-query (if possible)?

Solution

  • You need dynamic SQL for that:

    DO
$do$
BEGIN
EXECUTE format($f$ALTER DATABASE %I SET x.a_var TO 'a_value'$f$, current_database());
END
$do$;

    Using format() to escape the db name safely while being at it.

    BTW, to unset:

    ALTER DATABASE your_db RESET x.a_var;

    To see the current setting:

    SELECT current_setting('x.a_var');

    (The DB default is not active before you start a new session.)

    Related: