FOR SELECT INTO ... DO

Available in: PSQL

Description

Executes a SELECT statement and retrieves the result set. In each iteration of the loop, the field values of the current row are copied into local variables. Adding an AS CURSOR clause enables positioned deletion or update of the current row. FOR SELECT statements may be nested.

Syntax

FOR <select-stmt>
  INTO <var> [, <var> ...]
  [AS CURSOR name]
DO
  <psql-stmt>

<select-stmt>   ::= A valid SELECT statement.
<var>           ::= A PSQL variable name, optionally preceded by ":"
<psql-stmt>     ::= A single statement or a block of PSQL code.

Examples

create procedure shownums 
  returns (aa int, bb int, sm int, df int)
as
begin
  for select distinct a, b from numbers order by a, b
    into :aa, :bb
  do
  begin
    sm = aa + bb;
    df = aa - bb;
    suspend;
  end
end

create procedure relfields
  returns (relation char(32), pos int, field char(32))
as
begin
  for select rdb$relation_name from rdb$relations
    into :relation
  do
  begin
  for select rdb$field_position + 1, rdb$field_name
      from rdb$relation_fields
    where rdb$relation_name = :relation
     order by rdb$field_position
     into :pos, :field
    do
    begin
      if (pos = 2) then relation = ' "'; -- for nicer output
     suspend;
    end
  end
end

back to top of page

AS CURSOR clause

Available in: PSQL

Added in: IB

Description

The optional AS CURSOR clause creates a named cursor that can be referenced (after WHERE CURRENT OF) within the FOR SELECT loop in order to update or delete the current row. This feature was already added in InterBase, but not mentioned in the Language Reference.

Example

create procedure deltown (towntodelete varchar(24))
  returns (town varchar(24), pop int)
as
begin
  for select town, pop from towns into :town, :pop as cursor tcur do
  begin
  if (town = towntodelete)
    then delete from towns where current of tcur;
    else suspend;
  end
end

Notes: