8.12. Other Functions

Functions that don’t fit in any other category.

8.12.1. MAKE_DBKEY()

Creates a DBKEY value

Result typeBINARY(8)

Syntax 

  |MAKE_DBKEY (relation, recnum [, dpnum [, ppnum]])

Table 8.92. MAKE_DBKEY Function Parameters
ParameterDescription

relation

Relation name or relation id

recnum

Record number. Either absolute (if dpnum and ppnum are absent), or relative (if dpnum present)

dpnum

Data page number. Either absolute (if ppnum is absent) or relative (if ppnum present)

ppnum

Pointer page number.

MAKE_DBKEY creates a DBKEY value using a relation name or ID, record number, and (optionally) logical numbers of data page and pointer page.

ⓘ
Note

  1. If relation is a string expression or literal, then it is treated as a relation name, and the engine searches for the corresponding relation ID. The search is case-sensitive. In the case of string literal, relation ID is evaluated at query preparation time. In the case of expression, relation ID is evaluated at execution time. If the relation cannot be found, then error isc_relnotdef is raised.

  2. If relation is a numeric expression or literal, then it is treated as a relation ID and used as is, without verification against existing relations. If the argument value is negative or greater than the maximum allowed relation ID (65535 currently), then NULL is returned.

  3. Argument recnum represents an absolute record number in the relation (if the next arguments dpnum and ppnum are missing), or a record number relative to the first record, specified by the next arguments.

  4. Argument dpnum is a logical number of data page in the relation (if the next argument ppnum is missing), or number of data pages relative to the first data page addressed by the given ppnum.

  5. Argument ppnum is a logical number of pointer page in the relation.

  6. All numbers are zero-based. Maximum allowed value for dpnum and ppnum is 232 (4294967296). If dpnum is specified, then recnum can be negative. If dpnum is missing and recnum is negative, then NULL is returned. If ppnum is specified, then dpnum can be negative. If ppnum is missing and dpnum is negative, then NULL is returned.

  7. If any of specified arguments is NULL, the result is also NULL.

  8. Argument relation is described as INTEGER during query preparation, but it can be overridden by a client application as VARCHAR or CHAR. Arguments recnum, dpnum and ppnum are described as BIGINT.

8.12.1.1. Examples of MAKE_DBKEY

  1. Select record using relation name (note that relation name is uppercase)

      |select *
  |from rdb$relations
  |where rdb$db_key = make_dbkey('RDB$RELATIONS', 0)

  2. Select record using relation ID

      |select *
  |from rdb$relations
  |where rdb$db_key = make_dbkey(6, 0)

  3. Select all records physically residing on the first data page

      |select *
  |from rdb$relations
  |where rdb$db_key >= make_dbkey(6, 0, 0)
  |and rdb$db_key < make_dbkey(6, 0, 1)

  4. Select all records physically residing on the first data page of 6th pointer page

      |select *
  |from SOMETABLE
  |where rdb$db_key >= make_dbkey('SOMETABLE', 0, 0, 5)
  |and rdb$db_key < make_dbkey('SOMETABLE', 0, 1, 5)

8.12.2. RDB$ERROR()

Returns PSQL error information inside a WHEN …​ DO block

Available inPSQL

Result typeVaries (see table below)

Syntax 

  |RDB$ERROR (<context>)
  | 
  |<context> ::=
  |  GDSCODE | SQLCODE | SQLSTATE | EXCEPTION | MESSAGE

Table 8.93. Contexts
ContextResult typeDescription

GDSCODE

INTEGER

Firebird error code, see also GDSCODE

SQLCODE

INTEGER

(deprecated) SQL code, see also SQLCODE

SQLSTATE

CHAR(5) CHARACTER SET ASCII

SQLstate, see also SQLSTATE

EXCEPTION

VARCHAR(63) CHARACTER SET UTF8

Name of the active user-defined exception or NULL if the active exception is a system exception

MESSAGE

VARCHAR(1024) CHARACTER SET UTF8

Message text of the active exception

RDB$ERROR returns data of the specified context about the active PSQL exception. Its scope is confined to exception-handling blocks in PSQL (WHEN …​ DO). Outside the exception handling blocks, RDB$ERROR always returns NULL. This function cannot be called from DSQL.

8.12.2.1. Example of RDB$ERROR

  |BEGIN
  |  ...
  |WHEN ANY DO
  |  EXECUTE PROCEDURE P_LOG_EXCEPTION(RDB$ERROR(MESSAGE));
  |END

See alsoTrapping and Handling Errors, GDSCODE, SQLCODE, SQLSTATE

8.12.3. RDB$GET_TRANSACTION_CN()

Returns the commit number (CN) of a transaction

Result typeBIGINT

Syntax 

  |RDB$GET_TRANSACTION_CN (transaction_id)

Table 8.94. RDB$GET_TRANSACTION_CN Function Parameters
ParameterDescription

transaction_id

Transaction id

If the return value is greater than 1, it is the actual CN of the transaction if it was committed after the database was started.

The function can also return one of the following results, indicating the commit status of the transaction:

-2

Transaction is dead (rolled back)

-1

Transaction is in limbo

 0

Transaction is still active

 1

Transaction committed before the database started or less than the Oldest Interesting Transaction for the database

NULL

Transaction number supplied is NULL or greater than Next Transaction for the database

For more information about CN, consult the Firebird 4.0 Release Notes.

8.12.3.1. RDB$GET_TRANSACTION_CN Examples

  |select rdb$get_transaction_cn(current_transaction) from rdb$database;
  |select rdb$get_transaction_cn(123) from rdb$database;

8.12.4. RDB$ROLE_IN_USE()

Checks if a role is active for the current connection

Result typeBOOLEAN

Syntax 

  |RDB$ROLE_IN_USE (role_name)

Table 8.95. RDB$ROLE_IN_USE Function Parameters
ParameterDescription

role_name

String expression for the role to check. Case-sensitive, must match the role name as stored in RDB$ROLES

RDB$ROLE_IN_USE returns TRUE if the specified role is active for the current connection, and FALSE otherwise. Contrary to CURRENT_ROLE — which only returns the explicitly specified role — this function can be used to check for roles that are active by default, or cumulative roles activated by an explicitly specified role.

8.12.4.1. RDB$ROLE_IN_USE Examples

List currently active roles 

  |select rdb$role_name
  |from rdb$roles
  |where rdb$role_in_use(rdb$role_name);

See alsoCURRENT_ROLE

8.12.5. RDB$SYSTEM_PRIVILEGE()

Checks if the authorization of the current connection has a system privilege

Result typeBOOLEAN

Syntax 

  |RDB$SYSTEM_PRIVILEGE (<sys_privilege>)
  | 
  |<sys_privilege> ::=
  |  !! See CREATE ROLE !!

Table 8.96. RDB$SYSTEM_PRIVILEGE Function Parameters
ParameterDescription

sys_privilege

System privilege

RDB$SYSTEM_PRIVILEGE accepts a system privilege name and returns TRUE if the current connection has the given system privilege, and FALSE otherwise.

The authorization of the current connection is determined by privileges of the current user, the user PUBLIC, and the currently active roles (explicitly set or activated by default).

8.12.5.1. RDB$SYSTEM_PRIVILEGE Examples

  |select rdb$system_privilege(user_management) from rdb$database;

See alsoFine-grained System Privileges