FirebirdSQL logo
 CommentsProcedural SQL (PSQL) Statements 

INTO

Passes SELECT output into variables

Available in

PSQL

Syntax

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 INTO clause.

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.

Examples

  1. 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 CAST serves to make the average a real number;otherwise, since amount is presumably an integer field, SQL rules would truncate it to the nearest lower integer.

  2. 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;

Common Table Expressions (“WITH …​ AS …​ SELECT”)

Syntax
<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 ...]
Table 1. Arguments for Common Table Expressions
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.