Chapter 8. Built-in Scalar Functions

Upgraders: PLEASE READ!

Many functions that were implemented as external functions (UDFs) in earlier versions of Firebird have been progressively re-implemented as internal (built-in) functions. If some external function of the same name as a built-in one is declared in your database, it will remain there and it will override any internal function of the same name.

To make the internal function available, you need either to DROP the UDF, or to use ALTER EXTERNAL FUNCTION to change the declared name of the UDF.

8.1. Context Functions

8.1.1. `RDB$GET_CONTEXT()`

Available inDSQL, PSQL * As a declared UDF it should be available in ESQL

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

Parameter	Description
namespace	Namespace
varname	Variable name. Case-sensitive. Maximum length is 80 characters

Retrieves the value of a context variable from one of the namespaces SYSTEM, USER_SESSION and USER_TRANSACTION.

The namespacesThe USER_SESSION and USER_TRANSACTION namespaces are initially empty. The user can create and set variables in them 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. It contains 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 namespace, an error is raised. If you request a non-existing variable in one of the other 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 11.3, “CURRENT_ROLE” variable.
CURRENT_USER: Same as global Section 11.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.
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', 'WNET', 'XNET' or NULL.
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 11.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 11.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 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'))

8.1.2. `RDB$SET_CONTEXT()`

Available inDSQL, PSQL * As a declared UDF it should be available in ESQL

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

Parameter	Description
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)`

Creates, sets or unsets a variable in one of the user-writable namespaces USER_SESSION and USER_TRANSACTION.

The namespacesThe USER_SESSION and USER_TRANSACTION namespaces are initially empty. The user can create and set variables in them with RDB$SET_CONTEXT() and retrieve them with RDB$GET_CONTEXT(). The USER_SESSION context is bound to the current connection. Variables in USER_TRANSACTION only exist in the transaction in which they have been set. When the transaction ends, the context and all the variables defined in it are destroyed.

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 will 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.

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

Prev	Up	Next
7.8. Trapping and Handling Errors	Home	8.2. Mathematical Functions