Firebird Documentation Index → Firebird 2.5 Language Reference

Firebird Home

Firebird 2.5 Language Reference

Beta Release 1

Dmitry Filippov

Alexander Karpeykin

Alexey Kovyazin

Dmitry Kuzmenko

Denis Simonov

Paul Vinkenoog

Dmitry Yemanov

Paul Vinkenoog

The source of much copied reference material

26 April 2016, document version 0.904

Copyright © 2016 Firebird Project and all contributing authors, under the Public Documentation License Version 1.0. Please refer to the License Notice in the Appendix.

Abstract

This volume represents a compilation of topics concerning Firebird's SQL language written by members of the Russian-speaking community of Firebird developers and users. In 2014, it culminated in a language reference manual, in Russian. At the instigation of Alexey Kovyazin, a campaign was launched amongst Firebird users world-wide to raise funds to pay for a professional translation into English, from which translations into other languages would proceed under the auspices of the Firebird Documentation Project.

Table of Contents

1. About the Firebird SQL Language Reference

Subject Matter

Language Reference Updates
Gestation of the Big Book
Contributors

Acknowledgments

2. SQL Language Structure

Background to Firebird's SQL Language

SQL Flavours
SQL Dialects
Error Conditions

Basic Elements: Statements, Clauses, Keywords

Identifiers

Operators and Special Characters

3. Data Types and Subtypes

Integer Data Types

SMALLINT
INTEGER
BIGINT
Hexadecimal Format for Integer Numbers

Floating-Point Data Types

FLOAT
DOUBLE PRECISION

Fixed-Point Data Types

NUMERIC
DECIMAL

Data Types for Dates and Times

DATE
TIME
TIMESTAMP
Operations Using Date and Time Values

Character Data Types

Unicode
Client Character Set
Special Character Sets
Collation Sequence
Character Indexes
Character Types in Detail

Binary Data Types

BLOB Subtypes
BLOB Specifics
ARRAY Type

Special Data Types

SQL_NULL Data Type

Conversion of Data Types

Explicit Data Type Conversion
Implicit Data Type Conversion

Custom Data Types—Domains

Domain Attributes
Domain Override
Creating and Administering Domains

4. Common Language Elements

Expressions

Constants
SQL Operators
Conditional Expressions
NULL in Expressions
Subqueries

Assertions
Comparison Predicates
Existential Predicates
Quantified Subquery Predicates

5. Data Definition (DDL) Statements

CREATE DATABASE
ALTER DATABASE
DROP DATABASE

CREATE SHADOW
DROP SHADOW

CREATE DOMAIN
ALTER DOMAIN
DROP DOMAIN

CREATE TABLE
ALTER TABLE
DROP TABLE
RECREATE TABLE

CREATE INDEX
ALTER INDEX
DROP INDEX
SET STATISTICS

CREATE VIEW
ALTER VIEW
CREATE OR ALTER VIEW
DROP VIEW
RECREATE VIEW

CREATE TRIGGER
ALTER TRIGGER
CREATE OR ALTER TRIGGER
DROP TRIGGER
RECREATE TRIGGER

CREATE PROCEDURE
ALTER PROCEDURE
CREATE OR ALTER PROCEDURE
DROP PROCEDURE
RECREATE PROCEDURE

EXTERNAL FUNCTION

DECLARE EXTERNAL FUNCTION
ALTER EXTERNAL FUNCTION
DROP EXTERNAL FUNCTION

DECLARE FILTER
DROP FILTER

SEQUENCE (GENERATOR)

CREATE SEQUENCE
ALTER SEQUENCE
SET GENERATOR
DROP SEQUENCE

CREATE EXCEPTION
ALTER EXCEPTION
CREATE OR ALTER EXCEPTION
DROP EXCEPTION
RECREATE EXCEPTION

CREATE COLLATION
DROP COLLATION

CHARACTER SET

ALTER CHARACTER SET

CREATE ROLE
ALTER ROLE
DROP ROLE

COMMENT ON

6. Data Manipulation (DML) Statements

FIRST, SKIP
The SELECT Columns List
The FROM clause
Joins
The WHERE clause
The GROUP BY clause
The PLAN clause
UNION
ORDER BY
ROWS
FOR UPDATE [OF]
WITH LOCK
INTO
Common Table Expressions (“WITH ... AS ... SELECT”)

INSERT ... VALUES
INSERT ... SELECT
INSERT ... DEFAULT VALUES
The RETURNING clause
Inserting into BLOB columns

Using an alias
The SET Clause
The WHERE Clause
The ORDER BY and ROWS Clauses
The RETURNING Clause
Updating BLOB columns

UPDATE OR INSERT

The RETURNING clause

Aliases
WHERE
PLAN
ORDER BY and ROWS
RETURNING

EXECUTE PROCEDURE

“Executable” Stored Procedure

EXECUTE BLOCK

Input and output parameters
Statement Terminators

7. Procedural SQL (PSQL) Statements

Elements of PSQL

DML Statements with Parameters
Transactions
Module Structure

Stored Procedures

Benefits of Stored Procedures
Types of Stored Procedures
Creating a Stored Procedure
Modifying a Stored Procedure
Deleting a Stored Procedure

Stored Functions

PSQL Blocks

Firing Order (Order of Execution)
DML Triggers
Database Triggers
Creating Triggers
Modifying Triggers
Deleting a Trigger

Writing the Body Code

Assignment Statements
DECLARE CURSOR
DECLARE VARIABLE
BEGIN ... END
IF ... THEN ... ELSE
WHILE ... DO
LEAVE
EXIT
SUSPEND
EXECUTE STATEMENT
FOR SELECT
FOR EXECUTE STATEMENT
OPEN
FETCH
CLOSE
IN AUTONOMOUS TRANSACTION
POST_EVENT

Trapping and Handling Errors

System Exceptions
Custom Exceptions
EXCEPTION
WHEN ... DO

8. Built-in functions and Variables

Context variables

CURRENT_CONNECTION
CURRENT_DATE
CURRENT_ROLE
CURRENT_TIME
CURRENT_TIMESTAMP
CURRENT_TRANSACTION
CURRENT_USER
DELETING
GDSCODE
INSERTING
NEW
'NOW'
OLD
ROW_COUNT
SQLCODE
SQLSTATE
'TODAY'
'TOMORROW'
UPDATING
'YESTERDAY'
USER

Scalar Functions

Functions for Working with Context Variables
Mathematical Functions
Functions for Working with Strings
Date and Time Functions
Type Casting Functions
Functions for Bitwise Operations
Functions for Working with UUID
Functions for Working with Generators (Sequences)
Conditional Functions

Aggregate Functions

AVG()
COUNT()
LIST()
MAX()
MIN()
SUM()

9. Transaction Control

Transaction Statements

SET TRANSACTION
COMMIT
ROLLBACK
SAVEPOINT
RELEASE SAVEPOINT
Internal Savepoints
Savepoints and PSQL

10. Security

User Authentication

Specially Privileged Users
RDB$ADMIN Role
Administrators
SQL Statements for User Management

SQL Privileges

The Object Owner
Statements for Granting Privileges
Statements for Revoking Privileges

A. Supplementary Information

The RDB$VALID_BLR Field

How Invalidation Works

A Note on Equality

B. Exception Codes and Messages

SQLSTATE Error Codes and Descriptions
SQLCODE and GDSCODE Error Codes and Descriptions

C. Reserved Words and Keywords

Reserved words
Keywords

D. System Tables

E. Monitoring Tables

MON$ATTACHMENTS

Using MON$ATTACHMENTS to Kill a Connection

MON$CALL_STACK

MON$CONTEXT_VARIABLES

MON$DATABASE

MON$IO_STATS

MON$MEMORY_USAGE

MON$RECORD_STATS

MON$STATEMENTS

Using MON$STATEMENTS to Cancel a Query

MON$TRANSACTIONS

F. Character Sets and Collation Sequences

G. License notice

H. Document History

List of Tables

3.1. Overview of Data Types
3.2. Method of Physical Storage for Real Numbers
3.3. Arithmetic Operations for Date and Time Data Types
3.4. Collation Sequences for Character Set UTF8
3.5. Maximum Index Lengths by Page Size and Character Size
3.6. Conversions with CAST
3.7. Date and Time Literal Format Arguments
3.8. Literals with Predefined Values of Date and Time
3.9. Rules for Overriding Domain Attributes in Column Definition
4.1. Description of Expression Elements
4.2. Operator Type Precedence
4.3. Arithmetic Operator Precedence
4.4. Comparison Operator Precedence
4.5. Logical Operator Precedence
5.1. CREATE DATABASE Statement Parameters
5.2. ALTER DATABASE Statement Parameters
5.3. CREATE SHADOW Statement Parameters
5.4. DROP SHADOW Statement Parameter
5.5. CREATE DOMAIN Statement Parameters
5.6. ALTER DOMAIN Statement Parameters
5.7. CREATE TABLE Statement Parameters
5.8. ALTER TABLE Statement Parameters
5.9. DROP TABLE Statement Parameter
5.10. CREATE INDEX Statement Parameters
5.11. Maximum Indexes per Table
5.12. Maximum indexable (VAR)CHAR length
5.13. ALTER INDEX Statement Parameter
5.14. DROP INDEX Statement Parameter
5.15. SET STATISTICS Statement Parameter
5.16. CREATE VIEW Statement Parameters
5.17. ALTER VIEW Statement Parameters
5.18. CREATE OR ALTER VIEW Statement Parameters
5.19. DROP VIEW Statement Parameter
5.20. RECREATE VIEW Statement Parameters
5.21. CREATE TRIGGER Statement Parameters
5.22. ALTER TRIGGER Statement Parameters
5.23. DROP TRIGGER Statement Parameter
5.24. CREATE PROCEDURE Statement Parameters
5.25. ALTER PROCEDURE Statement Parameters
5.26. DROP PROCEDURE Statement Parameter
5.27. DECLARE EXTERNAL FUNCTION Statement Parameters
5.28. ALTER EXTERNAL FUNCTION Statement Parameters
5.29. DROP EXTERNAL FUNCTION Statement Parameter
5.30. DECLARE FILTER Statement Parameters
5.31. DROP FILTER Statement Parameter
5.32. CREATE SEQUENCE | CREATE GENERATOR Statement Parameter
5.33. ALTER SEQUENCE Statement Parameters
5.34. SET GENERATOR Statement Parameters
5.35. DROP SEQUENCE | DROP GENERATOR Statement Parameter
5.36. CREATE EXCEPTION Statement Parameters
5.37. ALTER EXCEPTION Statement Parameters
5.38. CREATE OR ALTER EXCEPTION Statement Parameters
5.39. DROP EXCEPTION Statement Parameter
5.40. RECREATE EXCEPTION Statement Parameters
5.41. CREATE COLLATION Statement Parameters
5.42. Specific Collation Attributes
5.43. DROP COLLATION Statement Parameters
5.44. ALTER CHARACTER SET Statement Parameters
5.45. CREATE ROLE Statement Parameter
5.46. COMMENT ON Statement Parameters
6.1. Arguments for the FIRST and SKIP Clauses
6.2. Arguments for the SELECT Columns List
6.3. Arguments for the FROM Clause
6.4. Arguments for JOIN Clauses
6.5. Arguments for the GROUP BY Clause
6.6. Arguments for the PLAN Clause
6.7. Arguments for the ORDER BY Clause
6.8. Arguments for the ROWS Clause
6.9. How TPB settings affect explicit locking
6.10. Arguments for Common Table Expressions
6.11. Arguments for the INSERT Statement Parameters
6.12. Arguments for the UPDATE Statement Parameters
6.13. Arguments for the UPDATE OR INSERT Statement Parameters
6.14. Arguments for the DELETE Statement Parameters
6.15. Arguments for the MERGE Statement Parameters
6.16. Arguments for the EXECUTE PROCEDURE Statement Parameters
6.17. Arguments for the EXECUTE BLOCK Statement Parameters
7.1. SET TERM Parameters
7.2. PSQL Block Parameters
7.3. Assignment Statement Parameters
7.4. DECLARE CURSOR Statement Parameters
7.5. DECLARE VARIABLE Statement Parameters
7.6. IF ... THEN ... ELSE Parameters
7.7. WHILE ... DO Parameters
7.8. LEAVE Statement Parameters
7.9. EXECUTE STATEMENT Statement Parameters
7.10. FOR SELECT Statement Parameters
7.11. FOR EXECUTE STATEMENT Statement Parameters
7.12. OPEN Statement Parameter
7.13. FETCH Statement Parameters
7.14. CLOSE Statement Parameter
7.15. IN AUTONOMOUS TRANSACTION Statement Parameter
7.16. POST_EVENT Statement Parameter
7.17. EXCEPTION Statement Parameters
7.18. WHEN ... DO Statement Parameters
8.1. CURRENT_TIME Parameter
8.2. CURRENT_TIME Parameter
8.3. RDB$GET_CONTEXT Function Parameters
8.4. Context variables in the SYSTEM namespace
8.5. RDB$SET_CONTEXT Function Parameters
8.6. ABS Function Parameter
8.7. ACOS Function Parameter
8.8. ASIN Function Parameter
8.9. ATAN Function Parameter
8.10. ATAN2 Function Parameters
8.11. CEIL[ING] Function Parameters
8.12. COS Function Parameter
8.13. COSH Function Parameter
8.14. COT Function Parameter
8.15. EXP Function Parameter
8.16. FLOOR Function Parameter
8.17. LN Function Parameter
8.18. LOG Function Parameters
8.19. LOG10 Function Parameter
8.20. MOD Function Parameters
8.21. POWER Function Parameters
8.22. ROUND Function Parameters
8.23. SIGN Function Parameter
8.24. SIN Function Parameter
8.25. SINH Function Parameter
8.26. SQRT Function Parameter
8.27. TAN Function Parameter
8.28. TANH Function Parameters
8.29. TRUNC Function Parameters
8.30. ASCII_CHAR Function Parameter
8.31. ASCII_VAL Function Parameter
8.32. BIT_LENGTH Function Parameter
8.33. CHAR[ACTER]_LENGTH Function Parameter
8.34. HASH Function Parameter
8.35. LEFT Function Parameters
8.36. LOWER Function ParameterS
8.37. LPAD Function Parameters
8.38. OCTET_LENGTH Function Parameter
8.39. OVERLAY Function Parameters
8.40. POSITION Function Parameters
8.41. REPLACE Function Parameters
8.42. REVERSE Function Parameter
8.43. RIGHT Function Parameters
8.44. RPAD Function Parameters
8.45. SUBSTRING Function Parameters
8.46. TRIM Function Parameters
8.47. UPPER Function Parameter
8.48. DATEADD Function Parameters
8.49. DATEDIFF Function Parameters
8.50. EXTRACT Function Parameters
8.51. Types and ranges of EXTRACT results
8.52. CAST Function Parameters
8.53. Possible Type-castings with CAST
8.54. BIN_AND Function Parameters
8.55. BIN_NOT Function Parameter
8.56. BIN_OR Function Parameters
8.57. BIN_SHL Function Parameters
8.58. BIN_SHR Function Parameters
8.59. BIN_XOR Function Parameters
8.60. CHAR_TO_UUID Function Parameter
8.61. UUID_TO_CHAR Function Parameters
8.62. GEN_ID Function Parameters
8.63. COALESCE Function Parameters
8.64. DECODE Function Parameters
8.65. IIF Function Parameters
8.66. MAXVALUE Function Parameters
8.67. MINVALUE Function Parameters
8.68. NULLIF Function Parameters
8.69. AVG Function Parameters
8.70. COUNT Function Parameters
8.71. LIST Function Parameters
8.72. MAX Function Parameters
8.73. MIN Function Parameters
8.74. SUM Function Parameters
9.1. SET TRANSACTION Statement Parameters
9.2. Compatibility of Access Options for RESERVING
9.3. COMMIT Statement Parameter
9.4. ROLLBACK Statement Parameters
9.5. SAVEPOINT Statement Parameter
9.6. RELEASE SAVEPOINT Statement Parameter
10.1. Parameters for RDB$ADMIN Role GRANT and REVOKE
10.2. Administrator (“Superuser”) Characteristics
10.3. CREATE USER Statement Parameters
10.4. ALTER USER Statement Parameters
10.5. DROP USER Statement Parameter
10.6. GRANT Statement Parameters
10.7. List of Privileges on Tables
10.8. REVOKE Statement Parameters
B.1. SQLSTATE Codes and Message Texts
B.2. SQLCODE and GDSCODE Error Codes and Message Texts (1)
B.3. SQLCODE and GDSCODE Error Codes and Message Texts (2)
B.4. SQLCODE and GDSCODE Error Codes and Message Texts (3)
B.5. SQLCODE and GDSCODE Error Codes and Message Texts (4)
D.1. List of System Tables
E.1. List of Monitoring Tables
F.1. Character Sets and Collation Sequences

Firebird Documentation Index → Firebird 2.5 Language Reference