The Firebird 5.0 Language Reference

`CREATE OR ALTER VIEW`

Creates a view if it doesn’t exist, or alters a view

Available in

DSQL

Syntax

CREATE OR ALTER VIEW viewname [<full_column_list>]
  AS <select_statement>
  [WITH CHECK OPTION]

<full_column_list> ::= (colname [, colname ...])

Table 1. `CREATE OR ALTER VIEW` Statement Parameters
Parameter	Description
viewname	Name of a view which may or may not exist
select_statement	SELECT statement
full_column_list	The list of columns in the view
colname	View column name.Duplicate column names are not allowed.

Use the CREATE OR ALTER VIEW statement for changing the definition of an existing view or creating it if it does not exist.Privileges for an existing view remain intact and dependencies are not affected.

The syntax of the CREATE OR ALTER VIEW statement corresponds with that of CREATE VIEW.

Example of `CREATE OR ALTER VIEW`

Creating the new view PRICE_WITH_MARKUP view or altering it if it already exists

CREATE OR ALTER VIEW PRICE_WITH_MARKUP (
  CODE_PRICE,
  COST,
  COST_WITH_MARKUP
) AS
SELECT
  CODE_PRICE,
  COST,
  COST * 1.15
FROM PRICE;

`DROP VIEW`

Drops a view

Available in

DSQL

Syntax

DROP VIEW viewname

Table 1. `DROP VIEW` Statement Parameter
Parameter	Description
viewname	View name

The DROP VIEW statement drops (deletes) an existing view.The statement will fail if the view has dependencies.

Who Can Drop a View?

The DROP VIEW statement can be executed by:

Administrators
The owner of the view
Users with the DROP ANY VIEW privilege

Example

Deleting the PRICE_WITH_MARKUP view

DROP VIEW PRICE_WITH_MARKUP;

`RECREATE VIEW`

Drops a view if it exists, and creates a view

Available in

DSQL

Syntax

RECREATE VIEW viewname [<full_column_list>]
  AS <select_statement>
  [WITH CHECK OPTION]

<full_column_list> ::= (colname [, colname ...])

Table 1. `RECREATE VIEW` Statement Parameters
Parameter	Description
viewname	View name.The maximum length is 63 characters
select_statement	SELECT statement
full_column_list	The list of columns in the view
colname	View column name.Duplicate column names are not allowed.

Creates or recreates a view.If there is a view with this name already, the engine will try to drop it before creating the new instance.If the existing view cannot be dropped, because of dependencies or insufficient rights, for example, RECREATE VIEW fails with an error.

Example of `RECREATE VIEW`

Creating the new view PRICE_WITH_MARKUP view or recreating it, if it already exists

RECREATE VIEW PRICE_WITH_MARKUP (
  CODE_PRICE,
  COST,
  COST_WITH_MARKUP
) AS
SELECT
  CODE_PRICE,
  COST,
  COST * 1.15
FROM PRICE;

CREATE OR ALTER VIEW

CREATE OR ALTER VIEW

Example of CREATE OR ALTER VIEW

docnext count = 5

DROP VIEW

Who Can Drop a View?

Example

RECREATE VIEW

Example of RECREATE VIEW

`CREATE OR ALTER VIEW`

Example of `CREATE OR ALTER VIEW`

`DROP VIEW`

`RECREATE VIEW`

Example of `RECREATE VIEW`