Firebird 5.0 Language Reference
Subject
Contributors
Authorship
Reporting Errors or Missing Content
Sponsors of the Russian Language Reference Manual
Acknowledgments
Contributing
SQL Flavours
SQL Dialects
Error Conditions
Background to Firebird's SQL Language
Basic Elements: Statements, Clauses, Keywords
Rules for Regular Identifiers
Rules for Delimited Identifiers
Identifiers
Literals
Operators and Special Characters
Comments
SMALLINT
INTEGER
BIGINT
INT128
Hexadecimal Format for Integer Numbers
Integer Data Types
FLOAT
REAL
DOUBLE PRECISION
Length of
DECFLOAT Use with Standard Functions Special Functions for
Floating-Point Data Types
NUMERIC
DECIMAL
Fixed-Point Data Types
DATE
TIME [WITHOUT TIME ZONE]
TIME WITH TIME ZONE
TIMESTAMP [WITHOUT TIME ZONE]
TIMESTAMP WITH TIME ZONE
Session Time Zone
Time Zone Format
Operations Using Date and Time Values
Virtual table
Package
Updating the Time Zone Database
Data Types for Dates and Times
Unicode
Client Character Set
Special Character Sets
Case-Insensitive Searching
UTF8
Character Indexes
BINARY
CHAR
VARBINARY
VARCHAR
NCHAR
Character Data Types
The
Use of Boolean Against Other Data Types
Boolean Data Type
BLOB
Binary Data Types
Specifying Explicit Boundaries for Dimensions
Adding More Dimensions PSQL Source for
Array Types
SQL_NULL
Special Data Types
Casting to a Domain
Casting to
Conversions Possible for the
Datetime Formats
Shorthand Casts for Datetime Data Types
Implicit Conversion During String Concatenation
Conversion of Data Types
Domain Attributes
Domain Override
Altering a Domain
Deleting (Dropping) a Domain
Custom Data Types — Domains
Use of Domains in Declarations
Use of Column Type in Declarations
BLOB Data Types Syntax
Array Data Types Syntax
Data Type Declaration Syntax
Alternative String Literals
Introducer Syntax for String Literals
Hexadecimal Notation for Numbers
Boolean Literals
Datetime Literals
Arithmetic Operators
Comparison Operators
Logical Operators
NEXT VALUE FOR
AT
Searched
Expressions Returning
NULL
Correlated Subqueries
Scalar Results
Expressions
Conditions
Wildcards
Using the Examples using
STARTING WITH
CONTAINING
Syntax: SQL Regular Expressions
Building Regular Expressions
Characters
Character Classes
Predefined Character Classes
Quantifiers
OR-ing Terms
Subexpressions
Escaping Special Characters
IS [NOT] DISTINCT FROM
Boolean
IS [NOT] NULL
EXISTS
IN
SINGULAR
ALL
ANY
Predicates
Data Definition (DDL) Statements
Using a Database Alias
Creating a Database on a Remote Server
Optional Parameters for
Specifying the Database Dialect
Who Can Create a Database
Examples Using
CREATE DATABASE
Who Can Alter the Database
Parameters for
Examples of
ALTER DATABASE
Who Can Drop a Database
Example of
DROP DATABASE
AUTO | MANUAL
Options for
Who Can Create a Shadow
CREATE SHADOW
Who Can Drop a Shadow
DROP SHADOW
Type-specific Details
Who Can Create a Domain
CREATE DOMAIN
ALTER DOMAIN
What
Who Can Alter a Domain
Who Can Drop a Domain
DROP DOMAIN
Character Columns
Setting a
Domain-based Columns
GENERATED ALWAYS
GENERATED BY DEFAULT
START WITH
INCREMENT
Computed Columns
Defining an Array Column
PRIMARY KEY
Foreign Key Actions
CHECK
NOT NULL
SQL SECURITY
Replication Management
Who Can Create a Table
CREATE TABLE
Restrictions on GTTs
Examples of Global Temporary Tables
External Table Example
Version Count Increments
Renaming a Column: the
Changing the Data Type of a Column: the
Changing the Position of a Column: the
Identity Type
RESTART
SET INCREMENT
DROP IDENTITY
Changing SQL Security
Attributes that Cannot Be Altered
Who Can Alter a Table?
ALTER TABLE
Who Can Drop a Table?
DROP TABLE
RECREATE TABLE
Who Can Create an Index?
Unique Indexes
Partial Indexes
Index Direction
Computed (Expression) Indexes
Maximum Indexes per Table
Character Index Limits
Parallelized Index Creation
CREATE INDEX
Who Can Alter an Index?
Use of
ALTER INDEX Examples
ALTER INDEX
Who Can Drop an Index?
DROP INDEX Example
DROP INDEX
Who Can Update Index Statistics?
Index Selectivity
Example Using SET STATISTICS
SET STATISTICS
Updatable Views
WITH CHECK OPTION
Who Can Create a View?
Examples of Creating Views
CREATE VIEW
Who Can Alter a View?
Example using
ALTER VIEW
CREATE OR ALTER VIEW
Who Can Drop a View?
Example
DROP VIEW
RECREATE VIEW
Statement Terminators
SQL Security
The Trigger Body
Who Can Create a DML Trigger?
Forms of Declaration
Phase
Row Events
Firing Order of Triggers
Who Can Create a Database Trigger?
Suppressing Database Triggers
Two-phase Commit
Some Caveats
Who Can Create a DDL Trigger?
Suppressing DDL Triggers
Examples of DDL Triggers
CREATE TRIGGER
Permitted Changes to Triggers
Who Can Alter a Trigger?
Examples using ALTER TRIGGER
ALTER TRIGGER
CREATE OR ALTER TRIGGER
Who Can Drop a Trigger?
DROP TRIGGER
RECREATE TRIGGER
Parameters
Variable, Cursor and Subroutine Declarations
External UDR Procedures
Who Can Create a Procedure
Examples
CREATE PROCEDURE
Who Can Alter a Procedure
ALTER PROCEDURE
CREATE OR ALTER PROCEDURE
Who Can Drop a Procedure
DROP PROCEDURE
RECREATE PROCEDURE
Deterministic functions
Function Body
External UDR Functions
Who Can Create a Function
CREATE FUNCTION
Who Can Alter a Function
ALTER FUNCTION
CREATE OR ALTER FUNCTION
Who Can Drop a Function
DROP FUNCTION
RECREATE FUNCTION
Clauses and Keywords
Who Can Create an External Function Examples using
DECLARE EXTERNAL FUNCTION
Who Can Alter an External Function
Examples using
ALTER EXTERNAL FUNCTION
Who Can Drop an External Function
DROP EXTERNAL FUNCTION
Procedure and Function Parameters
Who Can Create a Package
CREATE PACKAGE
Who Can Alter a Package
ALTER PACKAGE
CREATE OR ALTER PACKAGE
Who Can Drop a Package
DROP PACKAGE
RECREATE PACKAGE
Who Can Create a Package Body
CREATE PACKAGE BODY
Who Can Alter a Package Body
ALTER PACKAGE BODY
Who Can Drop a Package Body
DROP PACKAGE BODY
RECREATE PACKAGE BODY
Specifying the Subtypes
Who Can Create a
DECLARE FILTER
Who Can Drop a
DROP FILTER
Who Can Create a Sequence?
CREATE SEQUENCE
Who Can Alter a Sequence?
ALTER SEQUENCE
CREATE OR ALTER SEQUENCE
Who Can Drop a Sequence?
DROP SEQUENCE
RECREATE SEQUENCE
Who Can Use a
SET GENERATOR
Who Can Create an Exception
CREATE EXCEPTION
Who Can Alter an Exception
ALTER EXCEPTION
CREATE OR ALTER EXCEPTION
Who Can Drop an Exception
DROP EXCEPTION
RECREATE EXCEPTION
How the Engine Detects the Collation
Specific Attributes
Who Can Create a Collation
Examples using CREATE COLLATION
CREATE COLLATION
Who Can Drop a Collation
DROP COLLATION
Who Can Alter a Character Set
ALTER CHARACTER SET
Who Can Add a Comment
COMMENT ON
Characteristics of
Examples of FIRST/SKIP
Selecting
Example using a derived table A more useful example
Inner vs. Outer Joins
Named columns joins
Natural joins
Mixing Explicit and Implicit Joins
A Note on Equality
Ambiguous field names in joins
Joins with
HAVING
Simple Plans
Composite Plans
UNION
Sorting Direction
Collation Order
NULLs Position
Ordering
Replacing of
Mixing
ROWS
FOR UPDATE [OF]
Usage with a
How the engine deals with
Caveats using
Examples using explicit locking
OPTIMIZE FOR
INTO
Recursive CTEs
Full
SELECT
INSERT … VALUES
INSERT … SELECT
INSERT … DEFAULT VALUES
OVERRIDING
Inserting into
INSERT
Using an alias
RETURNING Example (DSQL)
Updating
UPDATE
UPDATE OR INSERT
Aliases
WHERE
PLAN
ORDER BY
SKIP LOCKED
RETURNING
DELETE
MERGE
“Executable” Stored Procedure Examples of
EXECUTE PROCEDURE
Input and output parameters
EXECUTE BLOCK
DML Statements with Parameters
Transactions
The Module Header
The PSQL Module Body
The External Module Body
Elements of PSQL
Benefits of Stored Procedures
Executable Procedures
Selectable Procedures
Creating a Stored Procedure
Modifying a Stored Procedure
Dropping a Stored Procedure
Stored Procedures
Creating a Stored Function
Modifying a Stored Function
Dropping a Stored Function
Stored Functions
PSQL Blocks
Benefits of Packages
Creating a Package
Modifying a Package
Dropping a Package
Packages
Firing Order (Order of Execution)
Trigger Options
OLD
Database Triggers
Semantics
Creating Triggers
Modifying Triggers
Dropping a Trigger
Triggers
Example using assignment statements
Example of Management Statements in PSQL
Data Type for Variables
CHARACTER SET
Initializing a Variable
Examples of various ways to declare local variables
Cursor Idiosyncrasies
Examples Using Named Cursors
Examples of Sub-Functions
Examples of Sub-Procedures
BEGIN … END
IF
WHILE … DO
BREAK
LEAVE
CONTINUE
EXIT
SUSPEND
WITH {AUTONOMOUS | COMMON} TRANSACTION
WITH CALLER PRIVILEGES
Transaction Pooling
Exception Handling
Miscellaneous Notes
AS USER
Caveats with
The Undeclared Cursor
FOR EXECUTE STATEMENT
OPEN
FETCH
CLOSE
IN AUTONOMOUS TRANSACTION
POST_EVENT
RETURN
Writing the Body Code
System Exceptions
Custom Exceptions
EXCEPTION
Scope of a
Trapping and Handling Errors
RDB$SET_CONTEXT()
Context Functions
ABS()
ACOS()
ACOSH()
ASIN()
ASINH()
ATAN()
ATAN2()
ATANH()
CEIL()
COS()
COSH()
COT()
EXP()
FLOOR()
LN()
LOG()
LOG10()
MOD()
PI()
POWER()
RAND()
ROUND
SIGN()
SIN()
SINH()
SQRT()
TAN()
TANH()
TRUNC()
Mathematical Functions
ASCII_CHAR()
ASCII_VAL()
BIT_LENGTH
BLOB_APPEND
CHAR_LENGTH
LEFT()
LOWER
LPAD
OCTET_LENGTH
OVERLAY
POSITION
REPLACE
REVERSE
RIGHT()
RPAD
Positional
Regular Expression
TRIM
UNICODE_CHAR()
UNICODE_VAL()
UPPER
String and Binary Functions
DATEDIFF
WEEK
Date and Time Functions
“Shorthand” Syntax
Allowed Type Conversions
Casting Parameters
Casting to a Domain or its Type
Casting to a Column's Type
Cast Examples
Type Casting Functions
BIN_AND()
BIN_NOT()
BIN_OR()
BIN_SHL()
BIN_SHR()
BIN_XOR()
Bitwise Functions
CHAR_TO_UUID
GEN_UUID
UUID_TO_CHAR
UUID Functions
GEN_ID
Functions for Sequences (Generators)
COALESCE
DECODE
IIF
MAXVALUE
MINVALUE
NULLIF
Conditional Functions
COMPARE_DECFLOAT()
TOTALORDER()
Special Functions for
DECRYPT
ENCRYPT
RSA_DECRYPT
RSA_ENCRYPT
RSA_PRIVATE
RSA_PUBLIC
RSA_SIGN_HASH
RSA_VERIFY_HASH
Cryptographic Functions
Example of RDB$ERROR
RDB$GET_TRANSACTION_CN
RDB$ROLE_IN_USE
RDB$SYSTEM_PRIVILEGE
Other Functions
FILTER
AVG
COUNT
LIST
MAX
MIN
SUM
General-purpose Aggregate Functions
CORR
COVAR_POP
COVAR_SAMP
STDDEV_POP
STDDEV_SAMP
VAR_POP
VAR_SAMP
Statistical Aggregate Functions
REGR_AVGX()
REGR_AVGY()
REGR_COUNT()
REGR_INTERCEPT
REGR_R2()
REGR_SLOPE()
REGR_SXX()
REGR_SXY()
REGR_SYY()
Linear Regression Aggregate Functions
Aggregate Functions as Window Functions
Partitioning
Window Frames
Named Windows
CUME_DIST
DENSE_RANK
NTILE
PERCENT_RANK
RANK
ROW_NUMBER
Ranking Functions
FIRST_VALUE()
LAG
LAST_VALUE()
LEAD()
NTH_VALUE()
Navigational Functions
Aggregate Functions Inside Window Specification
Function
Procedure
RDB$BLOB_UTIL
RDB$PROFILER
RDB$TIME_ZONE_UTIL
CURRENT_CONNECTION
CURRENT_DATE
CURRENT_ROLE
CURRENT_TIME
CURRENT_TIMESTAMP
CURRENT_TRANSACTION
CURRENT_USER
DELETING
GDSCODE
INSERTING
LOCALTIME
LOCALTIMESTAMP
NEW
'NOW'
RESETTING
ROW_COUNT
SQLCODE
SQLSTATE
'TODAY'
'TOMORROW'
UPDATING
'YESTERDAY'
USER
Transaction Name
WAIT
NO WAIT
SNAPSHOT Sharing Snapshot Transactions
SNAPSHOT TABLE STABILITY
READ COMMITTED
Variants of
NO AUTO UNDO
RESTART REQUESTS
AUTO COMMIT
IGNORE LIMBO
RESERVING
COMMIT
ROLLBACK
ROLLBACK TO SAVEPOINT
SAVEPOINT
RELEASE SAVEPOINT
Internal Savepoints
Savepoints and PSQL
Transaction Statements
Security
Windows Hosts
The Database Owner Users with the
Specially Privileged Users
Doing the Same Task Using
Using
Using the
Auto Admin Mapping in Regular Databases
Auto Admin Mapping in the Security Database
RDB$ADMIN
Administrators
List of Valid System Privileges
Fine-grained System Privileges
Who Can Create a User
CREATE USER
Who Can Alter a User?
ALTER USER
CREATE OR ALTER USER
Who Can Drop a User?
DROP USER
The Object Owner
Who Can Create a Role
CREATE ROLE
Who Can Alter a Role
ALTER ROLE
Who Can Drop a Role
DROP ROLE
Packaging Privileges in a
Cumulative Roles
Default Roles
The User
Alternative Syntax Using
Examples of Granting the
Examples of Granting DDL Privileges
Database DDL Privileges
Examples of Granting Database DDL Privileges
Examples of Role Assignment
GRANT
Revoking the
Removing the Privilege to One or More Roles
Revoking Privileges That Were
Revoking
REVOKE
The Mapping Rule
Who Can Create a Mapping
CREATE MAPPING
Who Can Alter a Mapping
ALTER MAPPING
CREATE OR ALTER MAPPING
Who Can Drop a Mapping
DROP MAPPING
Encrypting a Database
Decrypting a Database
SET BIND
SET DECFLOAT ROUND
SET DECFLOAT TRAPS
Data Type Behaviour
Clauses of
New Connections
Who Can Alter the External Connections Pool
Connections Pool Management
SET ROLE
SET TRUSTED ROLE
Changing the Current Role
Setting the Idle Session Timeout
Determining the Timeout that is In Effect
Setting a Statement Timeout
Determining the Statement Timeout that is In Effect
Session Timeouts
SET TIME ZONE
Time Zone Management
SET OPTIMIZE
Optimizer Configuration
Error Handling
Reset Session State
SET DEBUG OPTION
Debugging
SQLSTATE Error Codes and Descriptions
SQLCODE and GDSCODE Error Codes and Descriptions
Reserved words
Keywords
RDB$AUTH_MAPPING
RDB$BACKUP_HISTORY
RDB$CHARACTER_SETS
RDB$CHECK_CONSTRAINTS
RDB$COLLATIONS
RDB$CONFIG
RDB$DATABASE
RDB$DB_CREATORS
RDB$DEPENDENCIES
RDB$EXCEPTIONS
RDB$FIELDS
RDB$FIELD_DIMENSIONS
RDB$FILES
RDB$FILTERS
RDB$FORMATS
RDB$FUNCTIONS
RDB$FUNCTION_ARGUMENTS
RDB$GENERATORS
RDB$INDEX_SEGMENTS
RDB$INDICES
RDB$KEYWORDS
RDB$LOG_FILES
RDB$PACKAGES
RDB$PAGES
RDB$PROCEDURES
RDB$PROCEDURE_PARAMETERS
RDB$PUBLICATIONS
RDB$PUBLICATION_TABLES
RDB$REF_CONSTRAINTS
RDB$RELATIONS
RDB$RELATION_CONSTRAINTS
RDB$RELATION_FIELDS
RDB$ROLES
RDB$SECURITY_CLASSES
RDB$TIME_ZONES
RDB$TRANSACTIONS
RDB$TRIGGER_TYPE
RDB$TRIGGERS
RDB$TRIGGER_MESSAGES
RDB$TYPES
RDB$USER_PRIVILEGES
RDB$VIEW_RELATIONS
MON$ATTACHMENTS
MON$COMPILED_STATEMENTS
MON$CALL_STACK
MON$CONTEXT_VARIABLES
MON$DATABASE
MON$IO_STATS
MON$MEMORY_USAGE
MON$RECORD_STATS
MON$STATEMENTS
MON$TABLE_STATS
MON$TRANSACTIONS
SEC$DB_CREATORS
SEC$GLOBAL_AUTH_MAPPING
SEC$USERS
SEC$USER_ATTRIBUTES
PLG$PROF_CURSORS
PLG$PROF_PSQL_STATS
PLG$PROF_PSQL_STATS_VIEW
PLG$PROF_RECORD_SOURCES
PLG$PROF_RECORD_SOURCE_STATS
PLG$PROF_RECORD_SOURCE_STATS_VIEW
PLG$PROF_REQUESTS
PLG$PROF_SESSIONS
PLG$PROF_STATEMENTS
PLG$PROF_STATEMENT_STATS_VIEW
PLG$SRP
PLG$USERS
Character Sets and Collations
License notice
Document History
Firebird ODBC/JDBC Driver 2.0 Manual
Features Supported
About the Firebird ODBC driver
Downloading the Driver
Getting the Right Firebird Client Library
Installing the Driver on Windows
Unpacking the Files
Building from Sources
Installing the Binary Package
Installing the Driver on Linux
The DSN Settings
The Services Button
Configuring a DSN on Windows
Testing the Configuration
Configuring a DSN on Linux
Read Sequence of the Keys
DBNAME for Embedded Connections
DBNAME Using Aliases
Connection Parameters
Multithreading
Locking
Ending Explicit Transactions
Two Phase Commit Transactions
More Transactions
MS DTC Transactions
Password Security
ODBC Cursor Library
Cursors
ARRAY
Usage with Clarion
Priming the Driver to Listen for Events
Showing Logs from the Interface
Exploring the ODBC Services Console
More Ways to Create a Database
Using the Services API
Documentation Licence Software Licence
Firebird 5.0 Release Candidate 2 Release Notes
Compatibility with Older Versions
Bug Reporting
Documentation
Complete In Firebird 5.0 Release Candidate 2 Complete In Firebird 5.0 Release Candidate 1 Complete In Firebird 5.0 Beta 1
Summary of New Features
Quick Links
Support for parallel operations
Inline minor ODS upgrade
More cursor-related details in the plan output
Denser compression of records
Compiled statement cache
SQL and PSQL profiler
New Minor ODS Number
New System Tables
New Columns in System Tables
ODS (On-Disk Structure) Changes
Main API Extensions
Extensions to various getInfo() Methods
Services API Extensions
Application Programming Interfaces
Non-reserved
New Keywords in Firebird 5.0
MaxParallelWorkers
ParallelWorkers
Parameters for Parallel Operations
MaxStatementCacheSize
OnDisconnectTriggerTimeout
DefaultProfilerPlugin
OptimizeForFirstRows
OuterJoinConversion
Other Parameters
Changed configuration parameters
cascade_replication
Allow macros in replication.conf
Replication Configuration Additions and Changes
RemotePipeName
TcpLoopbackFastPath
Removed configuration parameters
System privilege PROFILE_ANY_ATTACHMENT
Trace events before a valid security context is established
Support for partial indices
COMMENT ON MAPPING
Support for
Support multiple rows for DML RETURNING
Allow parenthesized query expressions
Support
Full SQL standard character string literal syntax
Full SQL standard binary string literal syntax
Changes to literals
Improvements to
UNICODE_CHAR
QUARTER
New Expressions and Built-in Functions
Allow subroutines to access variables/parameters defined at the outer/parent level
Monitoring
Unify display of system procedures and functions packages with other system objects
Display statement BLR Replication information added to
isql
Parallel backup/restore
gbak
Parallel sweep and ICU dependencies rebuild
ODS upgrade
gfix
Migrating the security database from Firebird 4.0 to 5.0
Multi-row RETURNING behaviour
SQL
Removal of WNET protocol
Removal of QLI
Core Engine
Server Crashes/Hangups
Firebird 5.0 Release Candidate 2: Bug Fixes
nbackup
Firebird 5.0 Release Candidate 1: Bug Fixes
Firebird 5.0 Beta 1 Release: Bug Fixes
Firebird 5.0 Project Teams
Licence Notice
Writing Firebird UDRs in Pascal
Firebird Database Cache Buffer
Practical Migration Guide to Firebird 5.0