Contents
set_config()
A function for temporarily setting a configuration parameter
set_config()
is a system function for temporarily setting a configuration parameter.
set_config()
was added in PostgreSQL 7.3.
Usage
set_config (setting_name
text,new_value
text,is_local
boolean ) → text
Temporarily sets the parameter setting_name
to new_value,
and returns new_value
. It is functionally equivalent to the SET
command.
is_local
must be specified, and has one the following effects:
- If
FALSE
, the changed setting applies to the current database session - If
TRUE
, the changed setting will only apply to the current transaction
To permanently alter a configuration parameter, use ALTER SYSTEM
.
Change history
- PostgreSQL 7.3
- added (commit 1ce03603)
Examples
Setting client_min_messages
to log
for the current session:
postgres=# SELECT set_config('client_min_messages', 'log', FALSE); set_config ------------ log (1 row) postgres=# SHOW client_min_messages; LOG: statement: SHOW client_min_messages; client_min_messages --------------------- log (1 row)
Starting a new session resets the setting:
postgres=# \c - postgres You are now connected to database "postgres" as user "postgres". postgres=# SHOW client_min_messages; client_min_messages --------------------- notice (1 row)
Setting client_min_messages
to log
for the current transaction:
postgres=# BEGIN; BEGIN postgres=*# SELECT set_config('client_min_messages', 'log', TRUE); set_config ------------ log (1 row) postgres=*# SHOW client_min_messages; LOG: statement: SHOW client_min_messages; client_min_messages --------------------- log (1 row)
Once the transaction is completed, the original value is restored:
postgres=*# ROLLBACK ; LOG: statement: ROLLBACK ; ROLLBACK postgres=# SHOW client_min_messages; client_min_messages --------------------- notice (1 row)
Attempting to set an invalid value for a configuration parameter:
postgres=# SELECT set_config('client_min_messages', 'foo', TRUE); ERROR: invalid value for parameter "client_min_messages": "foo" HINT: Available values: debug5, debug4, debug3, debug2, debug1, log, notice, warning, error.
Attempting to set an invalid configuration parameter:
postgres=# SELECT set_config('foo', 'bar', TRUE); ERROR: unrecognized configuration parameter "foo"
References
- PostgreSQL documentation: Configuration Settings Functions