CREATE VIEW

Warning

SingleStore 9.0 gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.

SingleStore supports both writable and non-unwritable views. By default, views in SingleStore are non-writable. Attempts to run INSERT, UPDATE, or DELETE queries on a non-writable view result in an error. Instead, use the ALTER VIEW command or update data directly in the underlying tables from which the view is created.

Writable views support INSERT, UPDATE, and DELETE queries. Refer to Writable Views for more information.

Syntax

SQL

CREATE
    [DEFINER = { <user> | CURRENT_USER }]
    [SCHEMA_BINDING = { ON | OFF }]
    VIEW [IF NOT EXISTS] <view_name>
    AS <select_statement>

Writable Views

Inserting, updating, or deleting rows in a writable view also affects the rows in the underlying table.

Enable Writable Views

To enable creation of writable views, set the enable_writable_views global variable to 1.

SQL

SET GLOBAL enable_writable_views = 1;

Not all views are writable. After creating a view, query the information_schema.VIEWS information schema view to inspect if a view can be updated. For example:

SQL

SELECT TABLE_NAME, IS_UPDATABLE FROM information_schema.VIEWS
WHERE TABLE_NAME='shipped_products';

+------------------+--------------+
| TABLE_NAME       | IS_UPDATABLE |
+------------------+--------------+
| shipped_products | YES          |
+------------------+--------------+

Whether a view supports INSERT and DELETE queries depends on the view definition. Refer to Writable View Definition Limitations for more information.

Note

Information schema views are not writable.

Writable View Definition Limitations

To create a writable view that can be updated, the view definition must not contain the following:

Aggregate functions or window functions
DISTINCT, UNION, UNION ALL, EXCEPT, or INTERSECT clause
GROUP BY , HAVING, JOIN, or LIMIT clause
References to non-table entities, such as a view or derived table, in the FROM clause
Complex expression or derived (non-simple) column references in the SELECT subquery

To support INSERT queries, the view definition must conform to the restrictions specified earlier as well as the following:

The view definition must not contain multiple references to the same column from the underlying table.
The view definition must contain all the columns from the underlying table that do not have a default value.

Permissions Required

The user that defines the view (view definer) must have the INSERT, UPDATE, or DELETE permissions (or privilege) to the underlying table to perform the respective operations on the view. Additionally, the view definer needs the INSERT, UPDATE, or DELETE permissions to grant the respective permission on the view to other users. However, other users only need the write (INSERT, UPDATE, or DELETE) privilege for the view to perform the respective write operations on the view.

The following permissions apply to writable views: INSERT, UPDATE, and DELETE. For example:

SQL

GRANT INSERT, UPDATE, DELETE ON <view_name> TO <user>;

If the view definer's INSERT, UPDATE, or DELETE permission to the underlying table is revoked (for example by SUPER or admin users), then the respective INSERT, UPDATE, or DELETE privilege is revoked for other users as well.

Remarks

The following apply to both writable and non-writable views.

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.

SQL

CREATE VIEW person_view AS SELECT first_name, last_name
FROM table_name WHERE user_id = 'real_person';

SQL

CREATE VIEW active_items_view AS SELECT name
FROM items WHERE status = 'active';

SQL

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).

SQL

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;

Example: Create a Writable View

This example creates a writable view using the following orders table and then performs insert and delete operations on the view:

SQL

CREATE TABLE orders (
    product_id INT,
    quantity INT
);

INSERT INTO orders (product_id, quantity) VALUES
(101, 5),
(102, 3),
(101, 2),
(103, 4),
(102, 6),
(101, 3),
(104, 7),
(103, 2),
(102, 4),
(101, NULL);

Enable writable views:
SQL
```
SET GLOBAL enable_writable_views = 1;
```

Create a view:

SQL

CREATE VIEW shipped_products AS
SELECT product_id, quantity FROM orders
WHERE quantity IS NOT NULL;

Verify if the view is writable:

SQL

SELECT TABLE_NAME, IS_UPDATABLE FROM information_schema.VIEWS
WHERE TABLE_NAME='shipped_products';

+------------------+--------------+
| TABLE_NAME       | IS_UPDATABLE |
+------------------+--------------+
| shipped_products | YES          |
+------------------+--------------+

Query the view:

SQL

SELECT product_id, SUM(quantity) FROM shipped_products 
GROUP BY product_id;

+------------+---------------+
| product_id | SUM(quantity) |
+------------+---------------+
|        101 |            10 |
|        102 |            13 |
|        103 |             6 |
|        104 |             7 |
+------------+---------------+

Insert a row in the view, and then query the view and the underlying table:

SQL

INSERT INTO shipped_products VALUES (105, 10);

SELECT product_id, SUM(quantity) 
FROM shipped_products GROUP BY product_id;

+------------+---------------+
| product_id | SUM(quantity) |
+------------+---------------+
|        105 |            10 |
|        101 |            10 |
|        102 |            13 |
|        103 |             6 |
|        104 |             7 |
+------------+---------------+

SQL

SELECT product_id, SUM(quantity) 
FROM orders GROUP BY product_id;

+------------+---------------+
| product_id | SUM(quantity) |
+------------+---------------+
|        105 |            10 |
|        101 |            10 |
|        102 |            13 |
|        103 |             6 |
|        104 |             7 |
+------------+---------------+

A new row with product_id as 105 is inserted in both the view and the underlying table.

Delete the row, and then query the view and the underlying table:

SQL

DELETE FROM shipped_products WHERE product_id = 105;

SELECT product_id, SUM(quantity) 
FROM shipped_products GROUP BY product_id;

+------------+---------------+
| product_id | SUM(quantity) |
+------------+---------------+
|        101 |            10 |
|        102 |            13 |
|        103 |             6 |
|        104 |             7 |
+------------+---------------+

SQL

SELECT product_id, SUM(quantity) 
FROM orders GROUP BY product_id;

+------------+---------------+
| product_id | SUM(quantity) |
+------------+---------------+
|        101 |            10 |
|        102 |            13 |
|        103 |             6 |
|        104 |             7 |
+------------+---------------+

The row with product_id as 105 is deleted from both the view and the underlying table.

CREATE VIEW

On this page

Syntax

Writable Views

Enable Writable Views

Writable View Definition Limitations

Permissions Required

Remarks

Examples

Example: Create a Writable View

Was this article helpful?

On this page

Was this article helpful?

CREATE VIEW

On this page

Syntax

Writable Views

Enable Writable Views

Writable View Definition Limitations

Permissions Required

Remarks

Examples

Example: Create a Writable View

Related Topics

Was this article helpful?

On this page

Was this article helpful?