Firebird 5.0 Language Reference
Revision History
| unspecified | 3 April 2025 |
Table of Contents
- 1. About the Firebird 5.0 Language Reference
- 2. SQL Language Structure
- 3. Data Types and Subtypes
- 3.1. Integer Data Types
- 3.2. Floating-Point Data Types
- 3.3. Fixed-Point Data Types
- 3.4. Data Types for Dates and Times
- 3.5. Character Data Types
- 3.6. Boolean Data Type
- 3.7. Binary Data Types
- 3.8. Array Types
- 3.9. Special Data Types
- 3.10. Conversion of Data Types
- 3.11. Custom Data Types — Domains
- 3.12. Data Type Declaration Syntax
- 4. Common Language Elements
- 4.1. Expressions
- 4.2. Predicates
- 4.2.1. Conditions
- 4.2.2. Comparison Predicates
- 4.2.3. Existential Predicates
- 4.2.4. Quantified Subquery Predicates
- 5. Data Definition (DDL) Statements
- 5.1.
DATABASE - 5.2.
SHADOW - 5.3.
DOMAIN - 5.4.
TABLE- 5.4.1.
CREATE TABLE- 5.4.1.1. Character Columns
- 5.4.1.2. Setting a
DEFAULTValue - 5.4.1.3. Domain-based Columns
- 5.4.1.4. Identity Columns (Autoincrement)
- 5.4.1.5. Computed Columns
- 5.4.1.6. Defining an Array Column
- 5.4.1.7. Constraints
- 5.4.1.8.
SQL SECURITYClause - 5.4.1.9. Replication Management
- 5.4.1.10. Who Can Create a Table
- 5.4.1.11.
CREATE TABLEExamples - 5.4.1.12. Global Temporary Tables (GTT)
- 5.4.1.13. External Tables
- 5.4.2.
ALTER TABLE- 5.4.2.1. Version Count Increments
- 5.4.2.2. The
ADDClause - 5.4.2.3. The
DROPClause - 5.4.2.4. The
DROP CONSTRAINTClause - 5.4.2.5. The
ALTER [COLUMN]Clause - 5.4.2.6. Renaming a Column: the
TOClause - 5.4.2.7. Changing the Data Type of a Column: the
TYPEClause - 5.4.2.8. Changing the Position of a Column: the
POSITIONClause - 5.4.2.9. The
DROP DEFAULTandSET DEFAULTClauses - 5.4.2.10. The
SET NOT NULLandDROP NOT NULLClauses - 5.4.2.11. The
COMPUTED [BY]orGENERATED ALWAYS ASClauses - 5.4.2.12. Changing Identity Columns
- 5.4.2.13. Changing SQL Security
- 5.4.2.14. Replication Management
- 5.4.2.15. Attributes that Cannot Be Altered
- 5.4.2.16. Who Can Alter a Table?
- 5.4.2.17. Examples Using
ALTER TABLE
- 5.4.3.
DROP TABLE - 5.4.4.
RECREATE TABLE
- 5.4.1.
- 5.5.
INDEX- 5.5.1.
CREATE INDEX - 5.5.2.
ALTER INDEX - 5.5.3.
DROP INDEX - 5.5.4.
SET STATISTICS
- 5.5.1.
- 5.6.
VIEW - 5.7.
TRIGGER - 5.8.
PROCEDURE - 5.9.
FUNCTION - 5.10.
EXTERNAL FUNCTION - 5.11.
PACKAGE - 5.12.
PACKAGE BODY - 5.13.
FILTER - 5.14.
SEQUENCE(GENERATOR) - 5.15.
EXCEPTION - 5.16.
COLLATION - 5.17.
CHARACTER SET - 5.18. Comments
- 5.1.
- 6. Data Manipulation (DML) Statements
- 6.1.
SELECT- 6.1.1.
FIRST,SKIP - 6.1.2. The
SELECTColumns List - 6.1.3. The
FROMclause - 6.1.4. Joins
- 6.1.5. The
WHEREclause - 6.1.6. The
GROUP BYclause - 6.1.7. The
WINDOWClause - 6.1.8. The
PLANClause - 6.1.9.
UNION - 6.1.10.
ORDER BY - 6.1.11.
ROWS - 6.1.12.
OFFSET,FETCH - 6.1.13.
FOR UPDATE [OF] - 6.1.14.
WITH LOCK - 6.1.15.
OPTIMIZE FOR - 6.1.16.
INTO - 6.1.17. Common Table Expressions (
)WITH … AS … SELECT - 6.1.18. Full
SELECTSyntax
- 6.1.1.
- 6.2.
INSERT - 6.3.
UPDATE - 6.4.
UPDATE OR INSERT - 6.5.
DELETE - 6.6.
MERGE - 6.7.
EXECUTE PROCEDURE - 6.8.
EXECUTE BLOCK
- 6.1.
- 7. Procedural SQL (PSQL) Statements
- 7.1. Elements of PSQL
- 7.2. Stored Procedures
- 7.3. Stored Functions
- 7.4. PSQL Blocks
- 7.5. Packages
- 7.6. Triggers
- 7.7. Writing the Body Code
- 7.7.1. Assignment Statements
- 7.7.2. Management Statements in PSQL
- 7.7.3.
DECLARE VARIABLE - 7.7.4.
DECLARE .. CURSOR - 7.7.5.
DECLARE FUNCTION - 7.7.6.
DECLARE PROCEDURE - 7.7.7.
BEGIN … END - 7.7.8.
IF … THEN … ELSE - 7.7.9.
WHILE … DO - 7.7.10.
BREAK - 7.7.11.
LEAVE - 7.7.12.
CONTINUE - 7.7.13.
EXIT - 7.7.14.
SUSPEND - 7.7.15.
EXECUTE STATEMENT - 7.7.16.
FOR SELECT - 7.7.17.
FOR EXECUTE STATEMENT - 7.7.18.
OPEN - 7.7.19.
FETCH - 7.7.20.
CLOSE - 7.7.21.
IN AUTONOMOUS TRANSACTION - 7.7.22.
POST_EVENT - 7.7.23.
RETURN
- 7.8. Trapping and Handling Errors
- 8. Built-in Scalar Functions
- 8.1. Context Functions
- 8.2. Mathematical Functions
- 8.2.1.
ABS() - 8.2.2.
ACOS() - 8.2.3.
ACOSH() - 8.2.4.
ASIN() - 8.2.5.
ASINH() - 8.2.6.
ATAN() - 8.2.7.
ATAN2() - 8.2.8.
ATANH() - 8.2.9.
CEIL(),CEILING() - 8.2.10.
COS() - 8.2.11.
COSH() - 8.2.12.
COT() - 8.2.13.
EXP() - 8.2.14.
FLOOR() - 8.2.15.
LN() - 8.2.16.
LOG() - 8.2.17.
LOG10() - 8.2.18.
MOD() - 8.2.19.
PI() - 8.2.20.
POWER() - 8.2.21.
RAND() - 8.2.22.
ROUND() - 8.2.23.
SIGN() - 8.2.24.
SIN() - 8.2.25.
SINH() - 8.2.26.
SQRT() - 8.2.27.
TAN() - 8.2.28.
TANH() - 8.2.29.
TRUNC()
- 8.2.1.
- 8.3. String and Binary Functions
- 8.3.1.
ASCII_CHAR() - 8.3.2.
ASCII_VAL() - 8.3.3.
BASE64_DECODE() - 8.3.4.
BASE64_ENCODE() - 8.3.5.
BIT_LENGTH() - 8.3.6.
BLOB_APPEND() - 8.3.7.
CHAR_LENGTH(),CHARACTER_LENGTH() - 8.3.8.
CRYPT_HASH() - 8.3.9.
HASH() - 8.3.10.
HEX_DECODE() - 8.3.11.
HEX_ENCODE() - 8.3.12.
LEFT() - 8.3.13.
LOWER() - 8.3.14.
LPAD() - 8.3.15.
OCTET_LENGTH() - 8.3.16.
OVERLAY() - 8.3.17.
POSITION() - 8.3.18.
REPLACE() - 8.3.19.
REVERSE() - 8.3.20.
RIGHT() - 8.3.21.
RPAD() - 8.3.22.
SUBSTRING() - 8.3.23.
TRIM() - 8.3.24.
UNICODE_CHAR() - 8.3.25.
UNICODE_VAL() - 8.3.26.
UPPER()
- 8.3.1.
- 8.4. Date and Time Functions
- 8.5. Type Casting Functions
- 8.6. Bitwise Functions
- 8.7. UUID Functions
- 8.8. Functions for Sequences (Generators)
- 8.9. Conditional Functions
- 8.10. Special Functions for
DECFLOAT - 8.11. Cryptographic Functions
- 8.12. Other Functions
- 9. Aggregate Functions
- 10. Window (Analytical) Functions
- 11. System Packages
- 12. Context Variables
- 12.1.
CURRENT_CONNECTION - 12.2.
CURRENT_DATE - 12.3.
CURRENT_ROLE - 12.4.
CURRENT_TIME - 12.5.
CURRENT_TIMESTAMP - 12.6.
CURRENT_TRANSACTION - 12.7.
CURRENT_USER - 12.8.
DELETING - 12.9.
GDSCODE - 12.10.
INSERTING - 12.11.
LOCALTIME - 12.12.
LOCALTIMESTAMP - 12.13.
NEW - 12.14.
'NOW' - 12.15.
OLD - 12.16.
RESETTING - 12.17.
ROW_COUNT - 12.18.
SQLCODE - 12.19.
SQLSTATE - 12.20.
'TODAY' - 12.21.
'TOMORROW' - 12.22.
UPDATING - 12.23.
'YESTERDAY' - 12.24.
USER
- 12.1.
- 13. Transaction Control
- 13.1. Transaction Statements
- 13.1.1.
SET TRANSACTION - 13.1.2.
COMMIT - 13.1.3.
ROLLBACK - 13.1.4.
SAVEPOINT - 13.1.5.
RELEASE SAVEPOINT - 13.1.6. Internal Savepoints
- 13.1.7. Savepoints and PSQL
- 13.1.1.
- 13.1. Transaction Statements
- 14. Security
- 14.1. User Authentication
- 14.2. SQL Statements for User Management
- 14.3. SQL Privileges
- 14.4.
ROLE - 14.5. Statements for Granting Privileges
- 14.5.1.
GRANT- 14.5.1.1. The
TOClause - 14.5.1.2. The
WITH GRANT OPTIONClause - 14.5.1.3. The
GRANTED BYClause - 14.5.1.4. Privileges on Tables and Views
- 14.5.1.5. The
EXECUTEPrivilege - 14.5.1.6. The
USAGEPrivilege - 14.5.1.7. DDL Privileges
- 14.5.1.8. Database DDL Privileges
- 14.5.1.9. Examples of Granting Database DDL Privileges
- 14.5.1.10. Assigning Roles
- 14.5.1.1. The
- 14.5.1.
- 14.6. Statements for Revoking Privileges
- 14.7. Mapping of Users to Objects
- 14.8. Database Encryption
- 14.9. SQL Security
- 15. Management Statements
- A. Supplementary Information
- B. Exception Codes and Messages
- C. Reserved Words and Keywords
- D. System Tables
- D.1.
RDB$AUTH_MAPPING - D.2.
RDB$BACKUP_HISTORY - D.3.
RDB$CHARACTER_SETS - D.4.
RDB$CHECK_CONSTRAINTS - D.5.
RDB$COLLATIONS - D.6.
RDB$CONFIG - D.7.
RDB$DATABASE - D.8.
RDB$DB_CREATORS - D.9.
RDB$DEPENDENCIES - D.10.
RDB$EXCEPTIONS - D.11.
RDB$FIELDS - D.12.
RDB$FIELD_DIMENSIONS - D.13.
RDB$FILES - D.14.
RDB$FILTERS - D.15.
RDB$FORMATS - D.16.
RDB$FUNCTIONS - D.17.
RDB$FUNCTION_ARGUMENTS - D.18.
RDB$GENERATORS - D.19.
RDB$INDEX_SEGMENTS - D.20.
RDB$INDICES - D.21.
RDB$KEYWORDS - D.22.
RDB$LOG_FILES - D.23.
RDB$PACKAGES - D.24.
RDB$PAGES - D.25.
RDB$PROCEDURES - D.26.
RDB$PROCEDURE_PARAMETERS - D.27.
RDB$PUBLICATIONS - D.28.
RDB$PUBLICATION_TABLES - D.29.
RDB$REF_CONSTRAINTS - D.30.
RDB$RELATIONS - D.31.
RDB$RELATION_CONSTRAINTS - D.32.
RDB$RELATION_FIELDS - D.33.
RDB$ROLES - D.34.
RDB$SECURITY_CLASSES - D.35.
RDB$TIME_ZONES - D.36.
RDB$TRANSACTIONS - D.37.
RDB$TRIGGERS - D.38.
RDB$TRIGGER_MESSAGES - D.39.
RDB$TYPES - D.40.
RDB$USER_PRIVILEGES - D.41.
RDB$VIEW_RELATIONS
- D.1.
- E. Monitoring Tables
- F. Security tables
- G. Plugin tables
- G.1.
PLG$PROF_CURSORS - G.2.
PLG$PROF_PSQL_STATS - G.3.
PLG$PROF_PSQL_STATS_VIEW - G.4.
PLG$PROF_RECORD_SOURCES - G.5.
PLG$PROF_RECORD_SOURCE_STATS - G.6.
PLG$PROF_RECORD_SOURCE_STATS_VIEW - G.7.
PLG$PROF_REQUESTS - G.8.
PLG$PROF_SESSIONS - G.9.
PLG$PROF_STATEMENTS - G.10.
PLG$PROF_STATEMENT_STATS_VIEW - G.11.
PLG$SRP - G.12.
PLG$USERS
- G.1.
- H. Character Sets and Collations
- I. License notice
- J. Document History