CREATE VIEW
On this page
Create an unmaterialized view.
Syntax
CREATE
[DEFINER = { <user> | CURRENT_USER }]
[SCHEMA_BINDING = { ON | OFF }]
VIEW <view_name>
AS <select_statement>
Remarks
-
Views in SingleStore are not writable.
Attempts to INSERT
,UPDATE
, orDELETE
data in a view will result in an error.Instead, use ALTER 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.
-
SingleStore does not support MySQL’s
CREATE OR REPLACE VIEW
syntax.To modify an existing view, use ALTER VIEW
. -
By default, SingleStore 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 including DEFINER = CURRENT_
andUSER SQL SECURITY DEFINER
in theCREATE VIEW
statement. -
Views can reference views or tables from other databases.
-
CREATE VIEW view_
will replacename AS SELECT * FROM table_ name . . . *
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 if table_
is altered between the execution ofname CREATE VIEW
andSELECT .
.. . FROM view_ name -
Any function (including
CURRENT_
andUSER() NOW()
) used in a view are evaluated during execution ofSELECT .
, not when the view is created.. . FROM view_ name -
The
DEFINER
clause tells SingleStore which user should be used for security checks when a view is referenced by a query.The default value is CURRENT_ USER. -
When
SCHEMA_
is set toBINDING ON
, 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_
is set toBINDING OFF
. -
The following note applies when the engine variable
table_
is set toname_ case_ sensitivity OFF
: 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 SingleStore Commands ).
-
This command causes implicit commits.
Refer to COMMIT for more information. -
Refer to 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_nameFROM table_name WHERE user_id = 'real_person';
CREATE VIEW active_items_view AS SELECT nameFROM items WHERE status = 'active';
CREATE VIEW discounted_items_view AS SELECT nameFROM 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_
and customers_
).
CREATE VIEW customer_orders AS SELECT o.id, c.last_name, c.first_nameFROM orders_db.orders o, customers_db.customers c WHERE o.customer_id = c.id;
Related Topics
Last modified: February 27, 2024