CREATE VIEW¶
CREATE VIEW
creates a new view in an existing database. A view is a virtual table.
Tip
- Use views to simplify complex queries or present only partial data to specific roles.
- If an underlying table has changed (new columns, changed names, etc.) - a view may be invalidated. To recompile the view, see SELECT RECOMPILE_VIEW(<view name>)
Permissions¶
The role must have the CREATE
permission at the database level, as well as SELECT
permissions for any tables referenced by the view.
Syntax¶
create_view_statement ::=
CREATE VIEW [schema_name].view_name [ column_list ]
AS
query
;
schema_name ::= identifier
view_name ::= identifier
column_list ::= ( { column_name [, ...] } )
column_name ::= identifier
Parameters¶
Parameter | Description |
---|---|
schema_name |
The name of the schema in which to create the view. |
view_name |
The name of the view to create, which must be unique inside the schema. |
column_list |
An optional comma separated list of column names for the view. If specified, these column names will override the column names in the response of the query in the AS query statement. |
AS query |
The select query to execute when the view is referenced. |
Examples¶
A simple view¶
CREATE VIEW only_agressive_animals AS
SELECT * FROM cool_animals WHERE is_agressive=true;
SELECT * FROM only_agressive_animals;
Overriding default column names¶
CREATE VIEW only_relaxed_animals (animal_id, animal_name, should_i_worry) AS
SELECT id, name, is_agressive FROM cool_animals WHERE is_agressive=false;