CREATE VIEW

CREATE VIEW defines a view with a given query.

A view logically represents a table formed by a given query. The view does not physically store the table but executes the query to produce the data whenever the view is accessed.

Syntax

CREATE VIEW <name>
[ WITH ( <view_option_name> [= <view_option_value>] [, ... ] ) ]
AS <query>

Parameters

  • name - the name of the view to be created. The name must be distinct from the names of all other schema objects.

  • query - the SELECT query, which will be used to produce the logical table the view represents.

  • WITH ( <view_option_name> [= <view_option_value>] [, ... ] ) specifies optional parameters for a view. The following parameters are supported:

    • security_invoker (Bool) causes the underlying base relations to be checked against the privileges of the user of the view rather than the view owner.

Notes

The security_invoker option must always be set to true because the default behavior for views is to execute the query on behalf of the view's creator, which is not supported yet.

The execution context of the view's query differs from the context of the enclosing SELECT. It does not see previously defined PRAGMAs, named expressions, etc. Most importantly, users must specify the tables (or views) they select from in the view's query by their schema-qualified names. You can see in the examples that the absolute path like /domain/database/path/to/underlying_table is used to specify the table from which a view reads data. The particular context of the view's query compilation might change in the upcoming releases.

If you wish to specify column names that you would like to see in the output of the view, you might do so by modifying the view's query:

CREATE VIEW view_with_a_renamed_column WITH (security_invoker = TRUE) AS
SELECT
    original_column_name AS custom_column_name
FROM `/domain/database/path/to/underlying_table`;

Asterisk (*) expansion in the view's query happens each time you read from the view. The list of columns returned by the following statement:

/*
CREATE VIEW view_with_an_asterisk WITH (security_invoker = TRUE) AS
SELECT
    *
FROM `/domain/database/path/to/underlying_table`;
*/

SELECT * FROM view_with_an_asterisk;

will change if the list of columns of the underlying_table is altered.

Examples

Create a view that will list only recent series from the series table:

CREATE VIEW recent_series WITH (security_invoker = TRUE) AS
SELECT
    *
FROM `/domain/database/path/to/series`
WHERE
    release_date > Date("2020-01-01");

Create a view that will list the titles of the first episodes of the recent series:

CREATE VIEW recent_series_first_episodes_titles WITH (security_invoker = TRUE) AS
SELECT
    episodes.title AS first_episode
FROM `/domain/database/path/to/recent_series`
    AS recent_series
JOIN `/domain/database/path/to/episodes`
    AS episodes
USING(series_id)
WHERE episodes.season_id = 1 AND episodes.episode_id = 1;

See also

Previous
WITH
Next
CREATE TOPIC