FirebirdSQL logo
 Data Definition Language (DDL)Procedural SQL (PSQL) 

Support multiple rows for DML RETURNING

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.

Allow parenthesized query expressions

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.
Example
(
  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
);