UNICODE_CHAR
and UNICODE_VAL
Adriano dos Santos Fernandes
UNICODE_CHAR
Returns the Unicode character with the specified code point.
UNICODE_CHAR( <number> )
Note
|
The argument to UNICODE_CHAR must be a valid Unicode code point and not in the range of high/low surrogates (0xD800 to 0xDFFF), otherwise it throws an error. |
select unicode_char(x) from y;
UNICODE_VAL
Returns the Unicode code point of the first character of the specified string, or zero if the string is empty.
UNICODE_VAL( <string> )
select unicode_val(x) from y;
QUARTER
added to EXTRACT
, FIRST_DAY
and LAST_DAY
The scalar functions EXTRACT
, FIRST_DAY
and LAST_DAY
now support the date part QUARTER
.
select extract(quarter from date '2023-09-21') as Q, first_day(of quarter from date '2023-09-21') as Q_START, last_day(of quarter from date '2023-09-21') as Q_END from rdb$database; Q Q_START Q_END ======= =========== =========== 3 2023-07-01 2023-09-30
SKIP LOCKED
clauseAdriano dos Santos Fernandes
Tracker ticket: #7350
SKIP LOCKED
can be used with SELECT … WITH LOCK
, UPDATE
and DELETE
statements.It makes the engine skip records locked by other transactions instead of waiting on them or raise conflict errors.
This is very useful to implement work queues where one or more processes post work to a table and issue an event, while workers listen for events and read/delete items from the table.Using SKIP LOCKED
multiple workers can get exclusive work items from the table without conflicts.
SELECT [FIRST ...] [SKIP ...] FROM <sometable> [WHERE ...] [PLAN ...] [ORDER BY ...] [{ ROWS ... } | {OFFSET ...} | {FETCH ...}] [FOR UPDATE [OF ...]] [WITH LOCK [SKIP LOCKED]] UPDATE <sometable> SET ... [WHERE ...] [PLAN ...] [ORDER BY ...] [ROWS ...] [SKIP LOCKED] [RETURNING ...] DELETE FROM <sometable> [WHERE ...] [PLAN ...] [ORDER BY ...] [ROWS ...] [SKIP LOCKED] [RETURNING ...]
Note
|
If a statement has both |
Examples:
Prepare metadata
create table emails_queue ( subject varchar(60) not null, text blob sub_type text not null ); set term !; create trigger emails_queue_ins after insert on emails_queue as begin post_event('EMAILS_QUEUE'); end! set term ;!
Sender application or routine
insert into emails_queue (subject, text) values ('E-mail subject', 'E-mail text...'); commit;
Client application
-- Client application can listen to event `EMAILS_QUEUE` to actually send e-mails using this query: delete from emails_queue rows 10 skip locked returning subject, text;
More than one instance of the application may be running, for example to load balance work.
WHEN NOT MATCHED BY SOURCE
in the MERGE
statementAdriano dos Santos Fernandes
Tracker ticket: #6681
<merge when> ::= <merge when matched> | <merge when not matched by target> | <merge when not matched by source> <merge when not matched by target> ::= WHEN NOT MATCHED [ BY TARGET ] [ AND <condition> ] THEN INSERT [ <left paren> <column list> <right paren> ] VALUES <left paren> <value list> <right paren> <merge when not matched by source> ::= WHEN NOT MATCHED BY SOURCE [ AND <condition> ] THEN { UPDATE SET <assignment list> | DELETE }
<merge when not matched by target>
is called when a source record matches no record in target.INSERT
will change the target table.
<merge when not matched by source>
is called when a target record matches no record in source.UPDATE
or DELETE
will change the target table.
MERGE INTO customers c USING new_customers nc ON (c.id = nc.id) WHEN MATCHED THEN UPDATE SET name = nc.name WHEN NOT MATCHED BY SOURCE THEN DELETE
Adriano dos Santos Fernandes
Tracker ticket: #6815
In DSQL, the RETURNING
clause is now able to return multiple rows for DML statements than can affect multiple rows.
See compatibility notes on RETURNING for more information.
Adriano dos Santos Fernandes
Tracker ticket: #6740
The DML syntax was extended to allow a parenthesized query expression (select including order by
, offset
and fetch
clauses, but without with
clause) to occur where previously only a query specification (select without with
, order by
, offset
and fetch
clauses) was allowed.
This allows more expressive queries, especially in UNION
statements, and offers more compatibility with statements generated by certain ORMs.
Note
|
Using parenthesized query expressions comes at a cost, as they consume an additional query context compared to a plain query specification.The maximum number of query contexts in a statement is 255. |
( select emp_no, salary, 'lowest' as type from employee order by salary asc fetch first row only ) union all ( select emp_no, salary, 'highest' as type from employee order by salary desc fetch first row only );
PLAN
and ORDER BY
on MERGE
The MERGE
statement now supports the PLAN
and ORDER BY
clauses.
MERGE INTO target [[AS] target_alias] USING <source> [[AS] source_alias] ON <join_condition> <merge_when> [<merge_when> ...] [PLAN <plan-expr>] [ORDER BY <ordering-list>] [RETURNING <returning_list> [INTO <variables>]]
PLAN
, ORDER BY
and ROWS
on UPDATE OR INSERT
The UPDATE OR INSERT
statement now supports the PLAN
, ORDER BY
and ROWS
clauses.
UPDATE OR INSERT INTO target [(<column_list>)] [<override_opt>] VALUES (<value_list>) [MATCHING (<column_list>)] [PLAN <plan-expr>] [ORDER BY <ordering-list>] [ROWS <m> [TO <n>]] [RETURNING <returning_list> [INTO <variables>]]
OPTIMIZE FOR
ClauseDmitry Yemanov
SELECT
statements now support the OPTIMIZE FOR
clause.
SELECT ... [WITH LOCK [SKIP LOCKED]] [OPTIMIZE FOR {FIRST | LAST} ROWS]
The OPTIMIZE FOR
clause can only occur on a top-level SELECT
.
This feature allows the optimizer to consider another (hopefully better) plan if only a subset or rows is fetched initially by the user application (with the remaining rows being fetched on demand), thus improving the response time.
It can also be specified at the session level using the SET OPTIMIZE
management statement.
The default behaviour can be specified globally using the OptimizeForFirstRows setting in firebird.conf or databases.conf.
Adriano dos Santos Fernandes
Tracker ticket: #5589
The syntax of character string literals was changed to support the full SQL standard syntax.This means a literal can be “interrupted” by whitespace or a comment.This can be used, for example, to break up a long literal over several lines, or provide inline comments.
<character string literal> ::= [ <introducer> <character set specification> ] <quote> [ <character representation>... ] <quote> [ { <separator> <quote> [ <character representation>... ] <quote> }... ] <separator> ::= { <comment> | <white space> }...
-- whitespace between literal select 'ab' 'cd' from RDB$DATABASE; -- output: 'abcd' -- comment and whitespace between literal select 'ab' /* comment */ 'cd' from RDB$DATABASE; -- output: 'abcd'
Adriano dos Santos Fernandes
Tracker ticket: #5588
The syntax of binary string literals was changed to support the full SQL standard syntax.This means a literal can contain spaces to separate hexadecimal characters, and it can be “interrupted” by whitespace or a comment.This can be used, for example, to make the hex string more readable by grouping characters, or to break up a long literal over several lines, or provide inline comments.
<binary string literal> ::= X <quote> [ <space>... ] [ { <hexit> [ <space>... ] <hexit> [ <space>... ] }... ] <quote> [ { <separator> <quote> [ <space>... ] [ { <hexit> [ <space>... ] <hexit> [ <space>... ] }... ] <quote> }... ]
-- Group per byte (whitespace inside literal) select _win1252 x'42 49 4e 41 52 59' from RDB$DATABASE; -- output: BINARY -- whitespace between literal select _win1252 x'42494e' '415259' from RDB$DATABASE; -- output: BINARY
Note
|
The usage of the |