Search code examples
postgresql

How can I execute ALTER DATABASE $current_database in PostgreSQL

I'm trying to do this in a SQL script that I feed into psql:

ALTER DATABASE dbname SET SEARCH_PATH TO myschema,public

but I need dbname to be dynamically set to the current database rather than hard coded.

Is this possible in PostgreSQL? I tried this but it doesn't work:

ALTER DATABASE (select current_database()) SET SEARCH_PATH TO myschema,public;
Solution

  • You cannot execute such a statement in plain SQL, where identifiers cannot be parametrized - and DDL statements do not allow any string interpolation / parameters at all.

    From psql

    While using the default interactive terminal psql, you can use:

    SELECT format('ALTER DATABASE %I SET search_path = myschema,public', current_database())\gexec

    See:

    Plain SQL

    From any client, EXECUTE dynamic SQL in a PL/pgSQL code block. The most basic form being a DO command:

    DO
$do$
BEGIN
   EXECUTE format('ALTER DATABASE %I SET search_path = myschema,public'
                , current_database());
END
$do$;

    format() with the specifier %I quotes the identifier safely if necessary. See:

    About ALTER DATABASE and search_path: