How can I execute ALTER DATABASE $ current_database in PostgreSQL

Question

I am trying to do this in a SQL script that I pass to psql:

ALTER DATABASE dbname SET SEARCH_PATH TO myschema,public

but I need to dbnamedynamically set the current database, not hard-coded.

Is this possible in PostgreSQL? I tried this, but it does not work:

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

+3

dan Apr 18 '12 at 15:27

1 answer

Erwin brandstetter · Accepted Answer · 2012-04-18T15:51:09+0000

You cannot execute such a statement in plain SQL, where identifiers cannot be parameterized.
You can write the plpgsql function to execute dynamic SQL .

In PostgreSQL 9.0 or later, you can also use the operator DO:

DO $$
BEGIN
EXECUTE '
ALTER DATABASE ' || current_database() || ' SET SEARCH_PATH TO myschema,public';
END; $$;