CODEWITHSUNDEEP
sqlpostgresql
Kuba
Kuba

Reputation: 3056

ALTER DATABASE on a current db without explicit db name?

I would like to be able to write sql query that changes the database to which I am currently logged in.

Example:

$ psql my_db
psql(9.1.1)
my_db=> ALTER DATABASE my_db SET some_variable = '0';
                       ^^^^^

Is there a way to avoid specifying the database name in this query?

Upvotes: 21

Views: 3733

Answers (3)

Eric
Eric

Reputation: 5355

Combining a few things together, including previous answers and the "\gset" technique mentioned in http://marxsoftware.blogspot.com/2015/08/postgresql-psql-variable-from-query.html, and the most streamlined way is:

-- note no semicolon after \gset:
select current_database() \gset
alter database :current_database set some_var = '0';

Upvotes: 4

user330315
user330315

Reputation:

If you're on 9.1 you can use:

DO $$
BEGIN
   execute 'alter database '||current_database()||' set some_var = ''0''';
END
$$;

Upvotes: 26

A.H.
A.H.

Reputation: 66243

If you are executing the script in psql you can use the substitution mechanism of psql: 

alter database :DBNAME SET ...

The documentation is here: http://www.postgresql.org/docs/current/interactive/app-psql.html#APP-PSQL-VARIABLES

Upvotes: 12

Related Questions