Firebird 2.5 Language Reference → Firebird 2.5 Language Reference

Firebird

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

12 May 2016, document version 0.905

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
Authorship
Acknowledgments

2. SQL Language Structure

Background to Firebird's SQL Language
Basic Elements: Statements, Clauses, Keywords
Identifiers
Literals
Operators and Special Characters
Comments

3. Data Types and Subtypes

Integer Data Types
Floating-Point Data Types
Fixed-Point Data Types
Data Types for Dates and Times
Character Data Types
Binary Data Types
Special Data Types
Conversion of Data Types
Custom Data Types—Domains

4. Common Language Elements

Expressions
Predicates

5. Data Definition (DDL) Statements

DATABASE
SHADOW
DOMAIN
TABLE
INDEX
VIEW
TRIGGER
PROCEDURE
EXTERNAL FUNCTION
FILTER
SEQUENCE (GENERATOR)
EXCEPTION
COLLATION
CHARACTER SET
ROLE
COMMENTS

6. Data Manipulation (DML) Statements

SELECT
INSERT
UPDATE
UPDATE OR INSERT
DELETE
MERGE
EXECUTE PROCEDURE
EXECUTE BLOCK

7. Procedural SQL (PSQL) Statements

Elements of PSQL
Stored Procedures
Stored Functions
PSQL Blocks
Triggers
Writing the Body Code
Trapping and Handling Errors

8. Built-in functions and Variables

Context variables
Scalar Functions
Aggregate Functions

9. Transaction Control

Transaction Statements

10. Security

User Authentication
SQL Privileges

A. Supplementary Information

The RDB$VALID_BLR Field
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
MON$CALL_STACK
MON$CONTEXT_VARIABLES
MON$DATABASE
MON$IO_STATS
MON$MEMORY_USAGE
MON$RECORD_STATS
MON$STATEMENTS
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 2.5 Language Reference → Firebird 2.5 Language Reference