SET_CONFIG

Set a configuration parameter for the session or transaction and return the new value.

Category: miscReturns: STRINGDialect: PostgreSql

Syntax

SET_CONFIG(name, value, is_local)

Description

## Overview Sets a configuration parameter to the specified value and returns the new value. The `is_local` flag controls scope: TRUE limits the change to the current transaction, FALSE makes the change persist for the session. Use SET_CONFIG to pass application-level state (tenant id, request id, feature flags) into SQL expressions where the calling application cannot bind directly. Custom namespaced parameters (any name containing a dot, such as `app.tenant_id`) are reserved for application use and can be read back with CURRENT_SETTING. ## Behavior - Returns the newly set value as a string. - Session-scoped changes last until the session ends or a subsequent SET_CONFIG overrides them. - Transaction-scoped changes are reverted at COMMIT or ROLLBACK. - Setting a reserved engine parameter may require privileges; unknown parameters are accepted if they look like custom namespaces. - Side effect: updates session or transaction state. ## Compatibility - PG-compat alias for `set_config` function.

Parameters

Name	Type	Description
`name`		Specifies the name of the configuration parameter. Custom namespaced parameters (for example, 'app.tenant_id') are supported and intended for application-level session state.
`value`		Specifies the new value as a string. Numeric and boolean settings are accepted in their string form.
`is_local`		When true, the change applies only to the current transaction and is rolled back at commit or rollback. When false, the change persists for the rest of the session.

Examples

-- Set a session-scoped custom parameter
SELECT SET_CONFIG('app.tenant_id', '42', false);  -- '42'

-- Set a transaction-scoped custom parameter
BEGIN;
SELECT SET_CONFIG('app.request_id', 'req-123', true);
SELECT CURRENT_SETTING('app.request_id');
COMMIT;

-- Set and read back
SELECT SET_CONFIG('app.env', 'staging', false);
SELECT CURRENT_SETTING('app.env');  -- 'staging'

-- Use custom settings in a row-level security predicate
SELECT * FROM crm.catalog.customers WHERE tenant_id = CURRENT_SETTING('app.tenant_id');

Pitfalls

Values are always strings. Numeric or boolean settings must be passed as strings ('true', '42') and re-parsed on the read side.
Transaction-scoped settings disappear at ROLLBACK as well as COMMIT. Do not assume the value persists after the transaction ends.
SET_CONFIG is a function call, but it has a session-level side effect. The optimizer will not reorder or dedupe SET_CONFIG calls across repeated references.
Reserved engine parameter names may require privileges to set and can raise an error instead of applying.