Contents
set_config()
set_config() is a system function for temporarily setting a configuration parameter.
set_config() was added in PostgreSQL 7.3.
Usage
set_config (setting_nametext,new_valuetext,is_localboolean ) → text
set_config() temporarily sets the parameter setting_name to new_value, and returns new_value. It is functionally equivalent to the SET command.
The parameter 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