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

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"

Categories

Configuration, System function

See also

SET, current_setting(), ALTER SYSTEM