[doc] Improve Explore > YSQL > Views (for Doc7752)

[lizayugabyte]

Incorporate Bryn's suggestions posted in https://github.com/yugabyte/yugabyte-db/pull/7840

The overview text is too wishy-washy. I recommend this:

« A view is a schema object that stores and names the text of an arbitrary complete SELECT statement. There are dedicated DDL statements to create, alter, and to drop a view. A view can be used just like a table in SELECT, INSERT, UPDATE and DELETE statements. The umbrella term for tables and views is "relation"; ysqlsh's \d metacommand lists both table and views, Depending on the complexity of the view's definition, change-making statements that target a view might cause a run-time error. Access to a view for users other than its owner is governed by privileges just as it is for a table.

See the account of CREATE VIEW , and the associated ALTER and DROP statements in the YSQL reference documentation. Notice that there is also a CREATE OR REPLACE variant for changing the definition of an existing view without losing any privileges that might have been granted to it. »

I'll leave it to you to spell the relative URL right.

I recommend removing everything that follows the "Overview" and replacing it with an account that uses this single flow. It's more realistic. And it demonstrates a non-updatable view.

drop table if exists departments cascade; create table departments( department_no integer primary key, name text not null);

drop table if exists employees cascade; create table employees( employee_no integer primary key, name text not null, department_no integer not null,

constraint employees_fk foreign key(department_no) references departments(department_no));

insert into departments(department_no, name) values (10, 'Marketing'), (20, 'Sales');

insert into employees(employee_no, name, department_no) values (1221, 'John Smith', 10), (1222, 'Bette Davis', 10), (1223, 'Lucille Ball', 20), (1224, 'John Zimmerman', 20);

-- Check what we have to date select e.employee_no, e.name, d.name from employees e inner join departments d using(department_no) order by 1;

Show the output. Then continue:

drop view if exists employees_view cascade; -- This view isn't very useful. But at least it's updatable. create view employees_view(employee_no, name) as select employee_no, name from employees;

update employees_view set name = 'Lucille Arnaz' where employee_no = 1223; select * from employees_view order by 1;

-- Access the view. select * from employees_view order by 1;

Then continue:

-- Make a more useful view. create or replace view employees_view(employee_no, name, department_name) as select e.employee_no, e.name, d.name from employees e inner join departments d using(department_no);

-- Access the modified view. select * from employees_view order by 1;

Show the output. Then show a failing update attempt.

-- This now fails with error number 55000: cannot update view "employees_view" update employees_view set name = 'Lucille Arnaz' where employee_no = 1223;

The "detail" says "Views that do not select from a single table or view are not automatically updatable."

And the "hint" says "To enable updating the view, provide an INSTEAD OF UPDATE trigger or an unconditional ON UPDATE DO INSTEAD rule."

It's straightforward to implement such triggers. Explaining how is beyond the scope of this section.

[lizayugabyte]

Another comment from Bryn: show the common case is for a view to encapsulate a "select" with a join(s).