Views#

What is a View?#

Views look and act like tables, but the data in views is materialized on execution using a view definition query that itself references concrete tables. The data is stored in the tables the views reference not the view itself.

How to use Views#

Views allow you to derive tables using SQL instead of storing a copy of all the data you might want to derive. As a simple example, in a table of employee’s salaries, you may store yearly salary but when using the table to calculate monthly salary, you use a view that divides the yearly salary by 12.

Note, accessing views is be slower than accessing a table itself because the database must compute the values returned.

Difference between Postgres Views and Doltgres Views#

There is no difference between Postgres and Doltgres views. They are functionally equivalent.

Interaction with Doltgres Version Control#

Doltgres view definitions are versioned in the dolt_schemas system table.

Example#

create table salaries (name varchar(255), salary int, primary key(name));
insert into salaries values ('Jim', 120000), ('Bob', 240000), ('Sally', 360000);
create view monthly_salaries as select name, salary/12 as monthly_pay from salaries;
select * from monthly_salaries order by monthly_pay asc;
+-------+-------------+
| name  | monthly_pay |
+-------+-------------+
| Jim   | 10000       |
| Bob   | 20000       |
| Sally | 30000       |
+-------+-------------+

Using as of with Views#

select dolt_add('-A');
select dolt_commit('-am', 'Created table and view');
+----------------------------------+
| hash                             |
+----------------------------------+
| trj7dm02r8c94nqpbphjgolhhsk37rkj |
+----------------------------------+
insert into salaries values ('Tim', 480000);
select * from monthly_salaries order by monthly_pay asc;
+-------+-------------+
| name  | monthly_pay |
+-------+-------------+
| Jim   | 10000       |
| Bob   | 20000       |
| Sally | 30000       |
| Tim   | 40000       |
+-------+-------------+
select * from monthly_salaries as of 'HEAD' order by monthly_pay asc;
+-------+-------------+
| name  | monthly_pay |
+-------+-------------+
| Jim   | 10000       |
| Bob   | 20000       |
| Sally | 30000       |
+-------+-------------+