Chapter 8. Built-in Scalar Functions

Unless explicitly mentioned otherwise in an Available in section, functions are available in DSQL and PSQL. Availability of built-in functions in ESQL is not tracked by this Language Reference.

8.1. Context Functions

8.1.1. RDB$GET_CONTEXT()

Retrieves the value of a context variable from a namespace

Result typeVARCHAR(255)

Syntax 

  |RDB$GET_CONTEXT ('<namespace>', <varname>)
  | 
  |<namespace> ::= SYSTEM | USER_SESSION | USER_TRANSACTION | DDL_TRIGGER
  |<varname>   ::= A case-sensitive quoted string of max. 80 characters

Table 8.1. RDB$GET_CONTEXT Function Parameters
ParameterDescription

namespace

Namespace

varname

Variable name; case-sensitive with a maximum length of 80 characters

The namespacesThe USER_SESSION and USER_TRANSACTION namespaces are initially empty. A user can create and set variables with RDB$SET_CONTEXT() and retrieve them with RDB$GET_CONTEXT(). The SYSTEM namespace is read-only. The DDL_TRIGGER namespace is only valid in DDL triggers, and is read-only. The SYSTEM and DDL_TRIGGER namespaces contain a number of predefined variables, shown below.

Return values and error behaviourIf the polled variable exists in the given namespace, its value will be returned as a string of max. 255 characters. If the namespace doesn’t exist or if you try to access a non-existing variable in the SYSTEM or DDL_TRIGGER namespace, an error is raised. If you request a non-existing variable in one of the user namespaces, NULL is returned. Both namespace and variable names must be given as single-quoted, case-sensitive, non-NULL strings.

8.1.1.1. The SYSTEM Namespace

Context variables in the SYSTEM namespace
CLIENT_ADDRESS

For TCP, this is the IP address. For XNET, the local process ID. For all other protocols this variable is NULL.

CLIENT_HOST

The wire protocol host name of remote client. Value is returned for all supported protocols.

CLIENT_PID

Process ID of remote client application.

CLIENT_PROCESS

Process name of remote client application.

CURRENT_ROLE

Same as global Section 12.3, “CURRENT_ROLE” variable.

CURRENT_USER

Same as global Section 12.7, “CURRENT_USER” variable.

DB_FILE_ID

Unique filesystem-level ID of the current database.

DB_GUID

GUID of the current database.

DB_NAME

Canonical name of current database; either the full path to the database or — if connecting via the path is disallowed — its alias.

DECFLOAT_ROUND

Rounding mode of the current connection used in operations with DECFLOAT values. See also SET DECFLOAT.

DECFLOAT_TRAPS

Exceptional conditions for the current connection in operations with DECFLOAT values that cause a trap. See also SET DECFLOAT.

EFFECTIVE_USER

Effective user at the point RDB$GET_CONTEXT is called; indicates privileges of which user is currently used to execute a function, procedure, trigger.

ENGINE_VERSION

The Firebird engine (server) version.

EXT_CONN_POOL_ACTIVE_COUNT

Count of active connections associated with the external connection pool.

EXT_CONN_POOL_IDLE_COUNT

Count of currently inactive connections available in the connection pool.

EXT_CONN_POOL_LIFETIME

External connection pool idle connection lifetime, in seconds.

EXT_CONN_POOL_SIZE

External connection pool size.

GLOBAL_CN

Most current value of global Commit Number counter.

ISOLATION_LEVEL

The isolation level of the current transaction: 'READ COMMITTED', 'SNAPSHOT' or 'CONSISTENCY'.

LOCK_TIMEOUT

Lock timeout of the current transaction.

NETWORK_PROTOCOL

The protocol used for the connection: 'TCPv4', 'TCPv6', 'XNET' or NULL.

PARALLEL_WORKERS

The maximum number of parallel workers of the connection.

READ_ONLY

Returns 'TRUE' if current transaction is read-only and 'FALSE' otherwise.

REPLICA_MODE

Replica mode of the database: 'READ-ONLY', 'READ-WRITE' and NULL.

REPLICATION_SEQUENCE

Current replication sequence (number of the latest segment written to the replication journal).

SESSION_ID

Same as global Section 12.1, “CURRENT_CONNECTION” variable.

SESSION_IDLE_TIMEOUT

Connection-level idle timeout, or 0 if no timeout was set. When 0 is reported the database ConnectionIdleTimeout from databases.conf or firebird.conf applies.

SESSION_TIMEZONE

Current session time zone.

SNAPSHOT_NUMBER

Current snapshot number for the transaction executing this statement. For SNAPSHOT and SNAPSHOT TABLE STABILITY, this number is stable for the duration of the transaction; for READ COMMITTED this number will change (increment) as concurrent transactions are committed.

STATEMENT_TIMEOUT

Connection-level statement timeout, or 0 if no timeout was set. When 0 is reported the database StatementTimeout from databases.conf or firebird.conf applies.

TRANSACTION_ID

Same as global Section 12.6, “CURRENT_TRANSACTION” variable.

WIRE_COMPRESSED

Compression status of the current connection. If the connection is compressed, returns TRUE; if it is not compressed, returns FALSE. Returns NULL if the connection is embedded.

WIRE_CRYPT_PLUGIN

If connection is encrypted - returns name of current plugin, otherwise NULL.

WIRE_ENCRYPTED

Encryption status of the current connection. If the connection is encrypted, returns TRUE; if it is not encrypted, returns FALSE. Returns NULL if the connection is embedded.

8.1.1.2. The DDL_TRIGGER Namespace

The DDL_TRIGGER namespace is valid only when a DDL trigger is running. Its use is also valid in stored procedures and functions when called by DDL triggers.

The DDL_TRIGGER context works like a stack. Before a DDL trigger is fired, the values relative to the executed command are pushed onto this stack. After the trigger finishes, the values are popped. So in the case of cascade DDL statements, when a user DDL command fires a DDL trigger and this trigger executes another DDL command with EXECUTE STATEMENT, the values of the DDL_TRIGGER namespace are the ones relative to the command that fired the last DDL trigger on the call stack.

Context variables in the DDL_TRIGGER namespace
EVENT_TYPE

event type (CREATE, ALTER, DROP)

OBJECT_TYPE

object type (TABLE, VIEW, etc)

DDL_EVENT

event name (<ddl event item>), where <ddl event item> is EVENT_TYPE || ' ' || OBJECT_TYPE

OBJECT_NAME

metadata object name

OLD_OBJECT_NAME

for tracking the renaming of a domain (see note)

NEW_OBJECT_NAME

for tracking the renaming of a domain (see note)

SQL_TEXT

sql statement text

ⓘ
Note

ALTER DOMAIN old-name TO new-name sets OLD_OBJECT_NAME and NEW_OBJECT_NAME in both BEFORE and AFTER triggers. For this command, OBJECT_NAME will have the old object name in BEFORE triggers, and the new object name in AFTER triggers.

8.1.1.3. Examples

  |select rdb$get_context('SYSTEM', 'DB_NAME') from rdb$database
  | 
  |New.UserAddr = rdb$get_context('SYSTEM', 'CLIENT_ADDRESS');
  | 
  |insert into MyTable (TestField)
  |  values (rdb$get_context('USER_SESSION', 'MyVar'))

See alsoSection 8.1.2, “RDB$SET_CONTEXT()”

8.1.2. RDB$SET_CONTEXT()

Creates, sets or clears a variable in one of the user-writable namespaces

Result typeINTEGER

Syntax 

  |RDB$SET_CONTEXT ('<namespace>', <varname>, <value> | NULL)
  | 
  |<namespace> ::= USER_SESSION | USER_TRANSACTION
  |<varname>   ::= A case-sensitive quoted string of max. 80 characters
  |<value>     ::= A value of any type, as long as it's castable
  |                to a VARCHAR(255)

Table 8.2. RDB$SET_CONTEXT Function Parameters
ParameterDescription

namespace

Namespace

varname

Variable name. Case-sensitive. Maximum length is 80 characters

value

Data of any type provided it can be cast to VARCHAR(255)

The namespacesThe USER_SESSION and USER_TRANSACTION namespaces are initially empty. A user can create and set variables with RDB$SET_CONTEXT() and retrieve them with Section 8.1.1, “RDB$GET_CONTEXT()”. The USER_SESSION context is bound to the current connection, the USER_TRANSACTION context to the current transaction.

Lifecycle

  • When a transaction ends, its USER_TRANSACTION context is cleared.

  • When a connection is closed, its USER_SESSION context is cleared.

  • When a connection is reset using ALTER SESSION RESET, the USER_TRANSACTION and USER_SESSION contexts are cleared.

Return values and error behaviourThe function returns 1 when the variable already existed before the call and 0 when it didn’t. To remove a variable from a context, set it to NULL. If the given namespace doesn’t exist, an error is raised. Both namespace and variable names must be entered as single-quoted, case-sensitive, non-NULL strings.

ⓘ
Note

  • The maximum number of variables in any single context is 1000.

  • All USER_TRANSACTION variables survive a ROLLBACK RETAIN (see ROLLBACK Options) or ROLLBACK TO SAVEPOINT unaltered, no matter at which point during the transaction they were set.

  • Due to its UDF-like nature, RDB$SET_CONTEXT can — in PSQL only — be called like a void function, without assigning the result, as in the second example above. Regular internal functions don’t allow this type of use.

  • ALTER SESSION RESET clears both USER_TRANSACTION and USER_SESSION contexts.

Examples 

  |select rdb$set_context('USER_SESSION', 'MyVar', 493) from rdb$database
  | 
  |rdb$set_context('USER_SESSION', 'RecordsFound', RecCounter);
  | 
  |select rdb$set_context('USER_TRANSACTION', 'Savepoints', 'Yes')
  |  from rdb$database

See alsoSection 8.1.1, “RDB$GET_CONTEXT()”