The CREATE VIEW statement creates a new view, which is a stored query represented as a virtual table.
By default, views created in a database cannot reference objects in a different database. To enable cross-database references for views, set the sql.cross_db_views.enabled cluster setting to true.
The CREATE VIEW statement performs a schema change. For more information about how online schema changes work in CockroachDB, see Online Schema Changes.
Required privileges
The user must have the CREATE privilege on the parent database and the SELECT privilege on any table(s) referenced by the view.
Synopsis
Parameters
| Parameter | Description |
|---|---|
MATERIALIZED |
Create a materialized view. |
IF NOT EXISTS |
Create a new view only if a view of the same name does not already exist. If one does exist, do not return an error. Note that IF NOT EXISTS checks the view name only. It does not check if an existing view has the same columns as the new view. |
OR REPLACE |
Create a new view if a view of the same name does not already exist. If a view of the same name already exists, replace that view. In order to replace an existing view, the new view must have the same columns as the existing view, or more. If the new view has additional columns, the old columns must be a prefix of the new columns. For example, if the existing view has columns a, b, the new view can have an additional column c, but must have columns a, b as a prefix. In this case, CREATE OR REPLACE VIEW myview (a, b, c) would be allowed, but CREATE OR REPLACE VIEW myview (b, a, c) would not. |
view_name |
The name of the view to create, which must be unique within its database and follow these identifier rules. When the parent database is not set as the default, the name must be formatted as database.name. |
name_list |
An optional, comma-separated list of column names for the view. If specified, these names will be used in the response instead of the columns specified in AS select_stmt. |
AS select_stmt |
The selection query to execute when the view is requested. Note that it is not currently possible to use * to select all columns from a referenced table or view; instead, you must specify specific columns. |
AS OF SYSTEM TIME |
When used with CREATE MATERIALIZED VIEW, populates the materialized view using historical data. This can reduce contention by leveraging follower reads. The timestamp must be within the garbage collection window. For more information, see AS OF SYSTEM TIME. |
opt_temp |
Defines the view as a session-scoped temporary view. For more information, see Temporary Views. Support for temporary views is in preview. |
Example
This example highlights one key benefit to using views: simplifying complex queries. For additional benefits and examples, see Views.
Setup
The following examples use the startrek demo database schema.
To follow along, run cockroach demo startrek to start a temporary, in-memory cluster with the startrek schema and dataset preloaded:
$ cockroach demo startrek
Create a view
The sample startrek database contains two tables, episodes and quotes. The table also contains a foreign key constraint, between the episodes.id column and the quotes.episode column. To count the number of famous quotes per season, you could run the following join:
> SELECT startrek.episodes.season, count(*)
FROM startrek.quotes
JOIN startrek.episodes
ON startrek.quotes.episode = startrek.episodes.id
GROUP BY startrek.episodes.season;
season | count
---------+--------
1 | 78
2 | 76
3 | 46
(3 rows)
Alternatively, to make it much easier to run this complex query, you could create a view:
> CREATE VIEW startrek.quotes_per_season (season, quotes)
AS SELECT startrek.episodes.season, count(*)
FROM startrek.quotes
JOIN startrek.episodes
ON startrek.quotes.episode = startrek.episodes.id
GROUP BY startrek.episodes.season;
The view is then represented as a virtual table alongside other tables in the database:
> SHOW TABLES FROM startrek;
schema_name | table_name | type | estimated_row_count
--------------+-------------------+-------+----------------------
public | episodes | table | 79
public | quotes | table | 200
public | quotes_per_season | view | 3
(3 rows)
Executing the query is as easy as SELECTing from the view, as you would from a standard table:
> SELECT * FROM startrek.quotes_per_season;
season | quotes
---------+---------
3 | 46
1 | 78
2 | 76
(3 rows)
Replace an existing view
You can create a new view, or replace an existing view, with CREATE OR REPLACE VIEW:
> CREATE OR REPLACE VIEW startrek.quotes_per_season (season, quotes)
AS SELECT startrek.episodes.season, count(*)
FROM startrek.quotes
JOIN startrek.episodes
ON startrek.quotes.episode = startrek.episodes.id
GROUP BY startrek.episodes.season
ORDER BY startrek.episodes.season;
> SELECT * FROM startrek.quotes_per_season;
season | quotes
---------+---------
3 | 46
1 | 78
2 | 76
(3 rows)
Create a view that references routines
Views can call both scalar and set-returning user-defined functions (UDFs) in their SELECT statements.
The following example builds a view over a table and two UDFs.
Create and populate a table:
CREATE TABLE xy (x INT, y INT);
INSERT INTO xy VALUES (1, 2), (3, 4), (5, 6);
Define a scalar and a set-returning UDF:
CREATE FUNCTION f_scalar() RETURNS INT LANGUAGE SQL AS $$
SELECT count(*) FROM xy;
$$;
CREATE FUNCTION f_setof() RETURNS SETOF xy LANGUAGE SQL AS $$
SELECT * FROM xy;
$$;
Create a view that references both functions:
CREATE VIEW v_xy AS
SELECT x, y, f_scalar() AS total_rows
FROM f_setof();
Query the view:
SELECT * FROM v_xy ORDER BY x;
x | y | total_rows
----+---+-------------
1 | 2 | 3
3 | 4 | 3
5 | 6 | 3
(3 rows)
Because the view depends on f_scalar and f_setof, attempting to rename either function returns an error:
ALTER FUNCTION f_scalar RENAME TO f_scalar_renamed;
ERROR: cannot rename function "f_scalar" because other functions or views ([movr.public.v_xy]) still depend on it
SQLSTATE: 0A000
Create a materialized view with historical data using AS OF SYSTEM TIME
You can create a materialized view using historical data with the AS OF SYSTEM TIME clause. This is useful for reducing contention by performing a follower read when populating the view.
Historical data is available only within the garbage collection window.
The following example creates a materialized view using the most recent data that is available for follower reads:
CREATE MATERIALIZED VIEW overdrawn_accounts
AS SELECT id, balance
FROM bank
WHERE balance < 0
AS OF SYSTEM TIME follower_read_timestamp();
You can also specify an explicit timestamp:
CREATE MATERIALIZED VIEW overdrawn_accounts
AS SELECT id, balance
FROM bank
WHERE balance < 0
AS OF SYSTEM TIME '-10s';
See also
- Selection Queries
- Views
SHOW CREATEALTER VIEWDROP VIEW- Online Schema Changes
AS OF SYSTEM TIME- Follower Reads
CREATE VIEW (Updated)
The CREATE VIEW statement has been enhanced to support the security_invoker option.
Updated Synopsis
CREATE [TEMPORARY | TEMP] VIEW [IF NOT EXISTS] view_name [( column_list )] [WITH ( option [= value] [, ....] )] AS select_stmt
New Parameters
| Parameter | Description | Required |
|---|---|---|
security_invoker |
controls whether the view runs with the permissions of the view owner (false) or the current user (true). Accepts true, false, 1, or 0. Defaults to true when specified without a value |
No |
The security_invoker option requires the enable_view_security_invoker feature flag to be enabled via the allow_view_with_security_invoker_clause session setting.
Examples
-- Enable the feature flag
SET allow_view_with_security_invoker_clause = on;
-- Create a view with security invoker enabled (default value)
CREATE VIEW security_view WITH ( security_invoker ) AS SELECT * FROM sensitive_table;
-- Create a view with security invoker explicitly set to true
CREATE VIEW user_permissions_view WITH ( security_invoker = true ) AS SELECT * FROM users;
-- Create a view with security invoker disabled (runs with view owner permissions)
CREATE VIEW admin_view WITH ( security_invoker = false ) AS SELECT * FROM admin_data;
-- Using integer values (1 = true, 0 = false)
CREATE VIEW numeric_view WITH ( security_invoker = 1 ) AS SELECT count(*) FROM transactions;
ALTER VIEW SET OPTIONS [NEEDS REVIEW]
Note: This feature is currently unimplemented. The grammar support has been added but attempting to use this syntax will result in an "unimplemented" error.
Synopsis
ALTER VIEW [IF EXISTS] view_name SET ( security_invoker = { true | false | 1 | 0 } )
Description
The ALTER VIEW SET OPTIONS statement would modify view options after creation, specifically the security_invoker setting that controls view permission behavior.
Parameters
| Parameter | Description | Required |
|---|---|---|
view_name |
the name of the view to modify | Yes |
security_invoker |
controls whether the view runs with the permissions of the view owner (false) or the current user (true). Accepts true, false, 1, or 0 |
Yes |
Current Status
-- This will return an unimplemented error
ALTER VIEW my_view SET ( security_invoker = false );
ERROR: at or near ")": syntax error: unimplemented: this syntax
HINT: You have attempted to use a feature that is not yet implemented.
See Also
Related Feature Flag
The security invoker functionality is controlled by the allow_view_with_security_invoker_clause session setting:
-- Enable security invoker support
SET allow_view_with_security_invoker_clause = on;
-- Verify the setting
SHOW allow_view_with_security_invoker_clause;
When this setting is disabled (default), attempting to create views with the security_invoker option will result in:
ERROR: security invoker views are not supported
[HUMAN REVIEW: The security invoker feature appears to be related to PostgreSQL-style security definer/invoker views, but the specific behavior and security implications should be verified against the intended implementation.]
