Examples using explicit locking
-
Simple:
SELECT * FROM DOCUMENT WHERE ID=? WITH LOCK;
-
Multiple rows, one-by-one processing with DSQL cursor:
SELECT * FROM DOCUMENT WHERE PARENT_ID=? FOR UPDATE WITH LOCK;
Examples of SELECT queries with different types of column lists
Simple:
SELECT * FROM DOCUMENT WHERE ID=? WITH LOCK;
Multiple rows, one-by-one processing with DSQL cursor:
SELECT * FROM DOCUMENT WHERE PARENT_ID=?
FOR UPDATE WITH LOCK;
OPTIMIZE FOR
SELECT ... [WITH LOCK [SKIP LOCKED]] OPTIMIZE FOR {FIRST | ALL} 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.
INTO
Passes SELECT
output into variables
PSQL
In PSQL the INTO
clause is placed at the end of the SELECT
statement.
SELECT [...] <column-list> FROM ... [...] [INTO <variable-list>] <variable-list> ::= <variable> [, <variable> ...] <variable> ::= varname | ':' varname
Note
|
The colon prefix before local variable names in PSQL is optional in the |
In PSQL code (triggers, stored procedures and executable blocks), the results of a SELECT
statement can be loaded row-by-row into local variables.It is often the only way to do anything with the returned values at all, unless an explicit or implicit cursor name is specified.The number, order and types of the variables must match the columns in the output row.
A “plain” SELECT
statement can only be used in PSQL if it returns at most one row, i.e. if it is a singleton select.For multi-row selects, PSQL provides the FOR SELECT
loop construct, discussed later in the PSQL chapter.PSQL also supports the DECLARE CURSOR
statement, which binds a named cursor to a SELECT
statement.The cursor can then be used to walk the result set.
Selecting aggregated values and passing them into previously declared variables min_amt
, avg_amt
and max_amt
:
select min(amount), avg(cast(amount as float)), max(amount)
from orders
where artno = 372218
into min_amt, avg_amt, max_amt;
Note
|
The |
A PSQL trigger that retrieves two values as a BLOB
field (using the LIST()
function) and assigns it INTO
a third field:
select list(name, ', ')
from persons p
where p.id in (new.father, new.mother)
into new.parentnames;
WITH … AS … SELECT
”)<query-expression> ::= [<with-clause>] <query-expression-body> [<order-by-clause>] [{ <rows-clause> | [<result-offset-clause>] [<fetch-first-clause>] }] <with-clause> ::= WITH [RECURSIVE] <with-list-element> [, <with-list-element> ...] <with-list-element> ::= query-name [(<column-name-list>)] AS (<query-expression>) <column-name-list> ::= column-name [, column-name ...]
Argument | Description |
---|---|
query-name |
Alias for a table expression |
column-name |
Alias for a column in a table expression |
A common table expression or CTE can be described as a virtual table or view, defined in a preamble to a main query, and going out of scope after the main query’s execution.The main query can reference any CTEs defined in the preamble as if they were regular tables or views.CTEs can be recursive, i.e. self-referencing, but they cannot be nested.