CREATE VIEW
Create an unmaterialized view.
Syntax
CREATE [DEFINER = { <user> | CURRENT_USER }] [SCHEMA_BINDING = { ON | OFF }] VIEW <view_name> AS <select_statement>
Remarks
Views in SingleStoreDB are not writable. Attempts to
INSERT
,UPDATE
, orDELETE
data in a view will result in an error. Instead, useALTER VIEW
or manipulate data directly in the underlying tables from which the view is constructed.Permissions: The user who created the view is granted the ability to
CREATE
,ALTER
, andSELECT
from the view.The
SELECT
permission on the view is required to query it.The following permissions apply to views:
SELECT
,CREATE VIEW
,ALTER VIEW
,DROP VIEW
,SHOW VIEW
Refer to the permissions matrix for details.
SingleStoreDB does not support MySQL’s
CREATE OR REPLACE VIEW
syntax. To modify an existing view, useALTER VIEW
.By default, SingleStoreDB restricts the ability to
CREATE
,ALTER
, orSELECT
from the view to the user who created the view and toSUPER
users. In MySQL terms, this is the equivalent of includingDEFINER = CURRENT_USER
andSQL SECURITY DEFINER
in theCREATE VIEW
statement.Views can reference views or tables from other databases.
CREATE VIEW view_name AS SELECT * FROM table_name ...
will replace*
with the column names at the time ofCREATE VIEW
execution rather than during execution ofSELECT ... FROM view_name
. It is important to consider this distinction iftable_name
is altered between the execution ofCREATE VIEW
andSELECT ... FROM view_name
.Any function (including
CURRENT_USER()
andNOW()
) used in a view are evaluated during execution ofSELECT ... FROM view_name
, not when the view is created.The
DEFINER
clause tells SingleStoreDB which user should be used for security checks when a view is referenced by a query. The default value is CURRENT_USER.When
SCHEMA_BINDING
is set toON
, objects referenced by the view cannot be dropped if the view exists; you would need to drop the view before dropping these objects. By default,SCHEMA_BINDING
is set toOFF
.The following note applies when the engine variable
table_name_case_sensitivity
is set toOFF
: After you create a view, you cannot create another view having the same view name with a different case. Refer to Database Object Case-Sensitivity for more information.This command can be run on the master aggregator node, or a child aggregator node (see Node Requirements for SingleStoreDB Commands ).
This command causes implicit commits. See COMMIT for more information.
See the Permission Matrix for the required permission.
Examples
The following examples create views that reference a table in the same database as the view.
CREATE VIEW person_view AS SELECT first_name, last_name FROM table_name WHERE user_id = 'real_person';
CREATE VIEW active_items_view AS SELECT name FROM items WHERE status = 'active';
CREATE VIEW discounted_items_view AS SELECT name FROM active_items_view WHERE discount = 1;
The following example creates a view that references two tables (orders
and customers
) that are located in different databases (orders_db
and customers_db
).
CREATE VIEW customer_orders AS SELECT o.id, c.last_name, c.first_name FROM orders_db.orders o, customers_db.customers c WHERE o.customer_id = c.id;
Related Topics