In YSQL, a view is the result set of a stored query, which can be queried in the same manner as a persistent database collection object.
Use the CREATE VIEW statement to create a view in a database. It defines the view name and the (select) statement defining it.
Example:
yugabyte=# CREATE TABLE employees (id INT PRIMARY KEY, name VARCHAR, hire_date DATE, level_of_coolness INT);
CREATE TABLE
yugabyte=# INSERT INTO employees VALUES (1, 'Jim', '02/28/2022', 6), (2, 'Jane', '01/01/2019',
8), (3, 'Lucy', '09/19/1990', 10);
INSERT 0 3
yugabyte=# CREATE VIEW super_cool_employees AS SELECT name, hire_date FROM employees WHERE level_of_cool >=8;
CREATE VIEW
yugabyte=# SELECT * FROM super_cool_employees;
name | hire_date
------+------------
Jane | 2019-01-01
Lucy | 1990-09-19
(2 rows)
You can add columns to a View using the CREATE OR REPLACE VIEW command.
yugabyte=# CREATE OR REPLACE VIEW super_cool_employees AS SELECT id, name, hire_date FROM employees WHERE level_of_coolness >=8;
ERROR: cannot change name of view column "name" to "id"
Uh oh. That didn’t work.
To add columns to a view, we have to add them to the end of the current list of columns…
yugabyte=# CREATE OR REPLACE VIEW super_cool_employees AS SELECT name, hire_date, id FROM employees WHERE level_of_coolness >=8;
CREATE VIEW
yugabyte=# SELECT * FROM super_cool_employees;
name | hire_date | id
------+------------+----
Jane | 2019-01-01 | 2
Lucy | 1990-09-19 | 3
(2 rows)
Have Fun!
