Firebird Documentation Index → Firebird 3.0.6 Release Notes → Procedural SQL (PSQL) → Extension of Colon Prefix Usage |
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:
to allow OLD/NEW fields in cursors to be read or assigned to and to assign them to variables.
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
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
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.
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.
Firebird Documentation Index → Firebird 3.0.6 Release Notes → Procedural SQL (PSQL) → Extension of Colon Prefix Usage |