FirebirdSQL logo
  • Home
  • Anglaiskeyboard_arrow_down
 INDEXTRIGGER 

Who Can Create a View?

The CREATE VIEW statement can be executed by:

The creator of a view becomes its owner.

To create a view, a non-admin user also needs at least SELECT access to the underlying table(s) and/or view(s), and the EXECUTE privilege on any selectable stored procedures involved.

To enable insertions, updates and deletions through the view, the creator/owner must also possess the corresponding INSERT, UPDATE and DELETE rights on the underlying object(s).

Granting other users privileges on the view is only possible if the view owner has these privileges on the underlying objects WITH GRANT OPTION.This will always be the case if the view owner is also the owner of the underlying objects.

Examples of Creating Views

  1. Creating view returning the JOB_CODE and JOB_TITLE columns only for those jobs where MAX_SALARY is less than $15,000.

    CREATE VIEW ENTRY_LEVEL_JOBS AS
SELECT JOB_CODE, JOB_TITLE
FROM JOB
WHERE MAX_SALARY < 15000;

  2. Creating a view returning the JOB_CODE and JOB_TITLE columns only for those jobs where MAX_SALARY is less than $15,000.Whenever a new record is inserted or an existing record is updated, the MAX_SALARY < 15000 condition will be checked.If the condition is not true, the insert/update operation will be rejected.

    CREATE VIEW ENTRY_LEVEL_JOBS AS
SELECT JOB_CODE, JOB_TITLE
FROM JOB
WHERE MAX_SALARY < 15000
WITH CHECK OPTION;

  3. Creating a view with an explicit column list.

    CREATE VIEW PRICE_WITH_MARKUP (
  CODE_PRICE,
  COST,
  COST_WITH_MARKUP
) AS
SELECT
  CODE_PRICE,
  COST,
  COST * 1.1
FROM PRICE;

  4. Creating a view with the help of aliases for fields in the SELECT statement (the same result as in Example 3).

    CREATE VIEW PRICE_WITH_MARKUP AS
SELECT
  CODE_PRICE,
  COST,
  COST * 1.1 AS COST_WITH_MARKUP
FROM PRICE;

  5. Creating a read-only view based on two tables and a stored procedure.

    CREATE VIEW GOODS_PRICE AS
SELECT
  goods.name AS goodsname,
  price.cost AS cost,
  b.quantity AS quantity
FROM
  goods
  JOIN price ON goods.code_goods = price.code_goods
  LEFT JOIN sp_get_balance(goods.code_goods) b ON 1 = 1;
See also

[fblangref50-ddl-view-alter], [fblangref50-ddl-view-crtoralter], [fblangref50-ddl-view-recreate], [fblangref50-ddl-view-drop]

docnext count = 10

ALTER VIEW

Alters a view

Available in

DSQL

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

<full_column_list> ::= (colname [, colname ...])
Table 1. ALTER VIEW Statement Parameters
Parameter Description

viewname

Name of an existing view

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 ALTER VIEW statement for changing the definition of an existing view.Privileges for views remain intact and dependencies are not affected.

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

Caution

Be careful when you change the number of columns in a view.Existing application code and PSQL modules that access the view may become invalid.For information on how to detect this kind of problem in stored procedures and trigger, see The RDB$VALID_BLR Field in the Appendix.

Who Can Alter a View?

The ALTER VIEW statement can be executed by:

  • Administrators

  • The owner of the view

  • Users with the ALTER ANY VIEW privilege

Example using ALTER VIEW

Altering the view PRICE_WITH_MARKUP
ALTER VIEW PRICE_WITH_MARKUP (
  CODE_PRICE,
  COST,
  COST_WITH_MARKUP
) AS
SELECT
  CODE_PRICE,
  COST,
  COST * 1.15
FROM PRICE;
See also

[fblangref50-ddl-view-create], [fblangref50-ddl-view-crtoralter], [fblangref50-ddl-view-recreate]

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;
See also

[fblangref50-ddl-view-create], [fblangref50-ddl-view-alter], [fblangref50-ddl-view-recreate]

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;
See also

[fblangref50-ddl-view-create], [fblangref50-ddl-view-recreate], [fblangref50-ddl-view-crtoralter]

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;
See also

[fblangref50-ddl-view-create], [fblangref50-ddl-view-drop], [fblangref50-ddl-view-crtoralter]