Syntax
CREATE VIEW viewname [<full_column_list>] AS <select_statement> [WITH CHECK OPTION] <full_column_list> ::= (colname [, colname ...])
Changed in: 2.5
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')
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.
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.
Syntax (partial)
CREATE VIEW viewname [<full_column_list>] AS SELECT <column_def> [, <column_def> ...] FROM ... [WITH CHECK OPTION] <full_column_list> ::= (colname [, colname ...]) <column_def> ::= {source_col | expr} [[AS] colalias]
Notes:
Changed in: 2.0
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:
create view vplanes (make, model) as select make, model from jets union select make, model from props union select make, model from gliders
The column names will be taken from the union. Of course you can still override them with a view column list.
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.
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 the 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 takes advantage of the previous behaviour. Such code should be corrected for Firebird 2.0 and higher, or mutations may not reach the table at all.
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: