SET_SYS_CONTEXT¶

Syntax¶

CALL SET_SYS_CONTEXT( '<namespace>', '<key>', '<value>' )

Arguments¶

'namespace'

The namespace in which to store the key-value pair. Supported namespaces:

• snowflake$session_attributes — Stores immutable custom session attributes. Attribute names are case-sensitive.

• Any other string (or NULL) — Treats the namespace as a prefix for a session variable name, similar to the SET command. Namespaces are case-sensitive.

'key'

The name of the attribute or variable to set. All key names are case-sensitive.

'value'

The value to assign. The value must be a string or an expression that evaluates to a string.

Returns¶

The function returns a VARCHAR value:

• For the snowflake$session_attributes namespace: Always returns NULL (because immutable attributes cannot have a previous value). If the attribute has already been set in the current session, the function raises an error instead.

• For other namespaces: Returns the previous value of the session variable, or NULL if the variable did not previously exist. This matches the behavior of the SET command.

Access control requirements¶

No special privileges are required to set custom session attributes. Any user can set attributes in their own session.

Usage notes¶

For snowflake$session_attributes namespace (immutable attributes):

• Attributes are immutable. Once an attribute is set, any attempt to set it again (even to the same value) will result in an error.

• Attribute names are case-sensitive. app_context, App_Context, and APP_CONTEXT are treated as three different attributes.

• Attributes are session-scoped. They persist for the duration of the session and are not visible to other sessions.

• To retrieve attribute values, use SYS_CONTEXT (snowflake$session_attributes namespace): SYS_CONTEXT('snowflake$session_attributes', '<key>').

For other namespaces (session variables):

• Variable names are case-sensitive. user_id and USER_ID are treated as different variables.

• Variables can be updated. Setting a variable that already exists returns the previous value and updates it with the new value.

• The namespace (if provided) is used as a prefix: SET_SYS_CONTEXT('myns', 'mykey', 'val') creates a variable named myns.mykey.

• Variables can be retrieved using SYS_CONTEXT('<namespace>', '<key>') with the exact case used when setting the variable.

General notes:

• If you are specifying the function call in a double-quoted string in a shell, escape the $ character with a backslash (\) so that $session_attributes is not interpreted as a shell variable.

Examples¶

Example 1: Immutable session attributes (snowflake$session_attributes namespace)

Set a custom attribute to track the application context:

CALL SET_SYS_CONTEXT('snowflake$session_attributes', 'app_context', 'production');

Retrieve the attribute value (note: attribute names are case-sensitive):

SELECT SYS_CONTEXT('snowflake$session_attributes', 'app_context');

+---------------------------------------------------------------+
| SYS_CONTEXT('snowflake$session_attributes', 'app_context')   |
|---------------------------------------------------------------|
| production                                                    |
+---------------------------------------------------------------+

Once an attribute is set, attempting to change it results in an error:

-- This will fail because the attribute is immutable
CALL SET_SYS_CONTEXT('snowflake$session_attributes', 'app_context', 'development');

SQL compilation error: Cannot overwrite context value: app_context

Attribute names are case-sensitive:

-- This succeeds because it's a different attribute name (different case)
CALL SET_SYS_CONTEXT('snowflake$session_attributes', 'APP_CONTEXT', 'staging');

SELECT SYS_CONTEXT('snowflake$session_attributes', 'app_context') AS lower_case,
       SYS_CONTEXT('snowflake$session_attributes', 'APP_CONTEXT') AS upper_case;

+------------+------------+
| LOWER_CASE | UPPER_CASE |
|------------+------------|
| production | staging    |
+------------+------------+

Example 2: Session variables (other namespaces)

Set a session variable with a namespace prefix:

CALL SET_SYS_CONTEXT('myapp', 'user_id', '12345');

+---------------------------------------------------+
| SET_SYS_CONTEXT('myapp', 'user_id', '12345')     |
|---------------------------------------------------|
| NULL                                              |
+---------------------------------------------------+

The variable is stored with the exact case provided: myapp.user_id. Retrieve it:

SELECT SYS_CONTEXT('myapp', 'user_id');

+----------------------------------+
| SYS_CONTEXT('myapp', 'user_id')  |
|----------------------------------|
| 12345                            |
+----------------------------------+

Update the variable (returns the previous value):

CALL SET_SYS_CONTEXT('myapp', 'user_id', '67890');

+---------------------------------------------------+
| SET_SYS_CONTEXT('myapp', 'user_id', '67890')     |
|---------------------------------------------------|
| 12345                                             |
+---------------------------------------------------+