CREATE 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, or DELETE 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, and SELECT 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, or SELECT from the view to the user who created the view and to SUPER users. In MySQL terms, this is the equivalent of including DEFINER = CURRENT_USER and SQL SECURITY DEFINER in the CREATE 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 of CREATE VIEW execution rather than during execution of SELECT ... FROM view_name. It is important to consider this distinction if table_name is altered between the execution of CREATE VIEW and SELECT ... FROM view_name.

• Any function (including CURRENT_USER() and NOW()) used in a view are evaluated during execution of SELECT ... FROM view_name, not when the view is created.

• The DEFINER clause tells SingleStore which user to use for security checks when a view is referenced by a query. The default value is CURRENT_USER.

• When SCHEMA_BINDING is set to ON, objects referenced by the view cannot be dropped if the view exists; the view must be dropped before dropping these objects. By default, SCHEMA_BINDING is set to OFF.

• The following note applies when the engine variable table_name_case_sensitivity is set to 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_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

• ALTER VIEW

• DROP VIEW