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

Question

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

score 1 · Accepted Answer · edited May 23 '17 at 12:31

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

1 Answers1