Firebird Documentation IndexFirebird 3.0.6 Release NotesProcedural SQL (PSQL) → Extension of Colon Prefix Usage
Firebird Home Firebird Home Prev: PSQL Cursor StabilizationFirebird Documentation IndexUp: Procedural SQL (PSQL)Next: SQLSTATE in Exception Handlers

Extension of Colon Prefix Usage

Adriano dos Santos Fernandes

PSQL Cursors as Variables
Colon Prefix as a Variable Marker

Hitherto, the colon (:) prefix has been used in PSQL to mark a reference to a variable in DML statements. Its use has been extended in Firebird 3 for two unrelated purposes:

  1. to allow OLD/NEW fields in cursors to be read or assigned to and to assign them to variables.

  2. to make variable assignment in both DML and PSQL statements in modules and blocks more flexible and, where needed, to resolve ambiguity between field names and variable names

PSQL Cursors as Variables

Referencing cursors as record variables is now supported in PSQL. Either explicit (DECLARE AS CURSOR) or implicit (FOR SELECT) PSQL cursors make their current record available via the cursor name, thus making the INTO clause optional.

In FOR SELECT loops, it requires the AS CURSOR clause to be specified. For example:

execute block as
begin
  for
    select id, x from t1 as cursor c1
  do begin
    for select id, x from t2 where x = :c1.x as cursor c2 do
    begin
          /* . . . */
    end
  end
end
      

Note

Notice the extension of the use of a colon (:) as a prefix to the referenced cursor field.

Another example

for
  select rdb$relation_id as id, rdb$relation_name as name
  from rdb$relations
  where rdb$view_blr is null
  as cursor tables
do begin
  out_id = tables.id;
  out_name = tables.name;
  suspend;
end
      

To avoid ambiguity, the colon prefix could be used:

  out_id = :tables.id;
  out_name = :tables.name;
/* or */
  :out_id = :tables.id;
  :out_name = :tables.name;
      

tables here is a cursor name and acts similarly to OLD/NEW in triggers.

Colon Prefix as a Variable Marker

It is now valid to apply the colon prefix to a variable on either side of an assignment statement in PSQL-only constructs.

Previously, these were valid assignments:

  var1 = :var2;
/* or */
  new.fld = :var;
      

whereas, these were invalid:

  :var1 = :var2;
/* or */
  :new.fld = :var;
      

The extension (CORE-4434) fixes this inconsistency.

This is now valid syntax:

create trigger t1 before insert on t1
as
  declare v integer;
begin
  :v = :old.n;
  :new.n = :v;
end
      

In fact, using this example, there is no difference between

 :v = :old.n;
/* and */
  v = :old.n;
      

Here, it is just syntactic sugar but, in other cases, it provides the means to resolve ambiguity between field name references and variable names.

Example of possible ambiguity

for
  select rdb$relation_id as id, rdb$relation_name as name
  from rdb$relations
  where rdb$view_blr is null
  as cursor tables
do begin
  out_id = :table.id;
  select tables.name from tables where tables.id = :tables.id into :out_name;
  suspend;
end
      

Inside the nested SELECT, tables is both a table name and a cursor name here, so the colon is used to resolve the ambiguity.

Prev: PSQL Cursor StabilizationFirebird Documentation IndexUp: Procedural SQL (PSQL)Next: SQLSTATE in Exception Handlers
Firebird Documentation IndexFirebird 3.0.6 Release NotesProcedural SQL (PSQL) → Extension of Colon Prefix Usage