Views can select from stored procedures

Description: In Firebird 2.5 and up, views can select from selectable stored procedures.

Example:

create view low_bones as
select id, name, description from them_bones('human')
where name in ('leg_bone', 'foot_bone', 'toe_bone')

Views can infer column names from derived tables or GROUP
BY

Changed in: 2.5

Description: In Firebird 2.5 and up, views can infer the names of columns from a derived table
or involved in a GROUP BY clause. Previously it was necessary to specify explicit
aliases for these columns (either per column or in a full list).

Examples:

create view tickle as
select t from (select t from tackle)

create view vstocks as
select kind, sum(stock) s from stocks
group by kind

In the second example, notice that it is still necessary to alias the
SUM column. Previous Firebird versions also required an
explicit alias for the KIND column.

Per-column aliases supported in view definition

Changed in: 2.1

Description: Firebird 2.1 and up allow the use of column aliases in the
SELECT statement. You can alias none, some or all of the columns;
each alias used becomes the name of the corresponding view column.

If the full column list is also present, specifying column aliases is futile
as they will be overridden by the names in the column list.

The full column list used to be mandatory for views whose
SELECT statement contains expression-based columns or
identical column names. Now you can omit the full column list, provided that you
alias such columns in the SELECT clause.

Full SELECT syntax supported

Changed in: 2.0, 2.5

Description: From Firebird 2.0 onward view definitions are considered full-fledged
SELECT statements. Consequently, the following elements are
(re)allowed in view definitions: FIRST, SKIP,
ROWS, ORDER BY, PLAN and
UNION.

Note

In Firebird 2.5 and up, it is no longer necessary to supply a view column list if
the view is based on a UNION:

The column names will be taken from the union. Of course you can still override
them with a view column list.

PLAN subclause disallowed in 1.5, reallowed in 2.0

Changed in: 1.5, 2.0

Description: Firebird versions 1.5.x forbid the use of a PLAN subclause in
a view definition. From 2.0 onward a PLAN is allowed again.

Triggers on updatable views block auto-writethrough

Changed in: 2.0

Description: In versions prior to 2.0, Firebird often did not block the automatic writethrough
to the underlying table if one or more triggers were defined on a naturally updatable
view. This could cause mutations to be performed twice unintentionally, sometimes
leading to data corruption and other mishaps. Starting at Firebird 2.0, this
misbehaviour has been corrected: now if you define a trigger on a naturally updatable
view, no mutations to the view will be automatically passed on to the table; either your
trigger takes care of that, or nothing will. This is in accordance with the description
in the InterBase 6 Data Definition Guide under
Updating views with triggers.

Warning

Some people have developed code that counts on or takes advantage of the prior
behaviour. Such code should be corrected for Firebird 2.0 and higher, or mutations may
not reach the table at all.

View with non-participating NOT NULL columns in base table can
be made insertable

Changed in: 2.0

Description: Any view whose base table contains one or more non-participating NOT
NULL columns is read-only by nature. It can be made updatable by the use of
triggers, but even with those, all INSERT attempts into such views
used to fail because the NOT NULL constraint on the base table was
checked before the view trigger got a chance to put things right. In Firebird 2.0 and up
this is no longer the case: provided the right trigger is in place, such views are now
insertable.

Example:

The view below would give validation errors for any insert attempts in
Firebird 1.5 and earlier. In Firebird 2.0 and up it is insertable:

create table base (x int not null, y int not null);
create view vbase as select x from base;
set term #;
create trigger bi_base for vbase before insert
as
begin
if (new.x is null) then new.x = 33;
insert into base values (new.x, 0);
end#
set term ;#

Notes:

Please notice that the problem described above only occurred for
NOT NULL columns that were left outside
the view.

Oddly enough, the problem would be gone if the base table itself had a
trigger converting NULL input to something valid. But then there was a risk that the insert would take place twice, due to
the auto-writethrough bug
that has also been fixed in Firebird 2.

ALTER VIEW

Available in: DSQL

Added in: 2.5

Description: Firebird 2.5 and up support ALTER VIEW, allowing you to change
a view's definition without having to drop it first. Existing dependencies are
preserved.