Firebird Documentation Index → Firebird 2.5 Language Ref. Update
Firebird Home Firebird Home Firebird Documentation IndexNext: Introduction

Firebird 2.5 Language Reference Update

Everything new in Firebird SQL since InterBase 6

Paul Vinkenoog et al.

8 Oct 2011, document version 1.1 — covers Firebird 2.5 and 2.5.1


Table of Contents

1. Introduction
Subject matter
Versions covered
Authorship
Acknowledgments
2. New in Firebird 2.5
Reserved words and keywords
Miscellany
Data types and subtypes
Data Definition Language (DDL)
Data Manipulation Language (DML)
PSQL statements
Security and access control
Context variables
Operators and predicates
Aggregate functions
Internal functions
3. Reserved words and keywords
Added since InterBase 6
Dropped since InterBase 6
Possibly reserved in future versions
4. Miscellaneous language elements
-- (single-line comment)
Hexadecimal notation for numerals
Hexadecimal notation for “binary” strings
Shorthand datetime casts
CASE construct
5. Data types and subtypes
BIGINT data type
BLOB data type
SQL_NULL data type
New character sets
Character set NONE handling changed
New collations
6. DDL statements
CHARACTER SET
COLLATION
COMMENT
DATABASE
DOMAIN
EXCEPTION
EXTERNAL FUNCTION
FILTER
INDEX
PROCEDURE
SEQUENCE or GENERATOR
TABLE
TRIGGER
VIEW
7. DML statements
DELETE
EXECUTE BLOCK
EXECUTE PROCEDURE
INSERT
MERGE
SELECT
UPDATE
UPDATE OR INSERT
8. Transaction control statements
RELEASE SAVEPOINT
ROLLBACK
SAVEPOINT
SET TRANSACTION
9. PSQL statements
BEGIN ... END blocks may be empty
BREAK
CLOSE cursor
DECLARE
EXCEPTION
EXECUTE PROCEDURE
EXECUTE STATEMENT
EXIT
FETCH cursor
FOR EXECUTE STATEMENT ... DO
FOR SELECT ... INTO ... DO
IN AUTONOMOUS TRANSACTION
LEAVE
OPEN cursor
PLAN allowed in trigger code
Subqueries as PSQL expressions
UDFs callable as void functions
WHERE CURRENT OF valid again for view cursors
10. Security and access control
ALTER ROLE
GRANT and REVOKE
The RDB$ADMIN role
AUTO ADMIN MAPPING
SQL user management commands
11. Context variables
CURRENT_CONNECTION
CURRENT_ROLE
CURRENT_TIME
CURRENT_TIMESTAMP
CURRENT_TRANSACTION
CURRENT_USER
DELETING
GDSCODE
INSERTING
NEW
'NOW'
OLD
ROW_COUNT
SQLCODE
SQLSTATE
UPDATING
12. Operators and predicates
NULL literals allowed as operands
|| (string concatenator)
ALL
ANY / SOME
IN
IS [NOT] DISTINCT FROM
NEXT VALUE FOR
SIMILAR TO
SOME
13. Aggregate functions
LIST()
MAX()
MIN()
14. Internal functions
ABS()
ACOS()
ASCII_CHAR()
ASCII_VAL()
ASIN()
ATAN()
ATAN2()
BIN_AND()
BIN_OR()
BIN_SHL()
BIN_SHR()
BIN_XOR()
BIT_LENGTH()
CAST()
CEIL(), CEILING()
CHAR_LENGTH(), CHARACTER_LENGTH()
CHAR_TO_UUID()
COALESCE()
COS()
COSH()
COT()
DATEADD()
DATEDIFF()
DECODE()
EXP()
EXTRACT()
FLOOR()
GEN_ID()
GEN_UUID()
HASH()
IIF()
LEFT()
LN()
LOG()
LOG10()
LOWER()
LPAD()
MAXVALUE()
MINVALUE()
MOD()
NULLIF()
OCTET_LENGTH()
OVERLAY()
PI()
POSITION()
POWER()
RAND()
RDB$GET_CONTEXT()
RDB$SET_CONTEXT()
REPLACE()
REVERSE()
RIGHT()
ROUND()
RPAD()
SIGN()
SIN()
SINH()
SQRT()
SUBSTRING()
TAN()
TANH()
TRIM()
TRUNC()
UPPER()
UUID_TO_CHAR()
15. External functions (UDFs)
abs
acos
addDay
addHour
addMilliSecond
addMinute
addMonth
addSecond
addWeek
addYear
ascii_char
ascii_val
asin
atan
atan2
bin_and
bin_or
bin_xor
ceiling
cos
cosh
cot
dow
dpower
floor
getExactTimestamp
i64round
i64truncate
ln
log
log10
lower
lpad
ltrim
mod
*nullif
*nvl
pi
rand
right
round, i64round
rpad
rtrim
sdow
sign
sin
sinh
sqrt
srand
sright
string2blob
strlen
substr
substrlen
tan
tanh
truncate, i64truncate
A. Notes
Character set NONE data accepted “as is
Understanding the WITH LOCK clause
A note on CSTRING parameters
Passing NULL to UDFs in Firebird 2
Maximum number of indices in different Firebird versions
The RDB$VALID_BLR field
B. Reserved words and keywords – full lists
Reserved words
Keywords
C. Document History
D. License notice

List of Tables

5.1. Character sets new in Firebird
5.2. Collations new in Firebird
6.1. Specific collation attributes
6.2. Maximum indexable (VAR)CHAR length
6.3. Max. indices per table, Firebird 2.0
7.1. NULLs placement in ordered columns
12.1. Comparison of [NOT] DISTINCT to “=” and “<>
14.1. Possible CASTs
14.2. Types and ranges of EXTRACT results
14.3. Context variables in the SYSTEM namespace
A.1. How TPB settings affect explicit locking
A.2. Max. indices per table in Firebird 1.0 – 2.0
Firebird Documentation IndexNext: Introduction
Firebird Documentation Index → Firebird 2.5 Language Ref. Update