A view:
is the named query based on a table.
can only have SELECT or VALUES statement so it cannot have other statements like INSERT, UPDATE, DELETE, etc otherwise there is error.
can have only single query.
can be called with FROM clause of
SELECTstatement.can be used with
INSERT,UPDATEorDELETEstatement to change the contents of its base table.cannot have zero or multiple queries otherwise there is error.
*The doc explains a view.
For example, you create person table as shown below:
CREATE TABLE person (
id INT,
first_name VARCHAR(20),
last_name VARCHAR(20),
age INT
);
Then, you insert 2 rows into person table as shown below:
INSERT INTO person (id, first_name, last_name, age)
VALUES (1, 'John', 'Smith', 27), (2, 'David', 'Miller', 32);
Now, you can create my_v view with SELECT statement as shown below:
CREATE VIEW my_v AS
SELECT first_name, age FROM person;
Then, you can call my_v with FROM clause of SELECT statement as shown below. *id and last_name are not usable:
postgres=# SELECT * FROM my_v;
first_name | age
------------+-----
John | 27
David | 32
(2 rows)
And, you can insert 2 rows to person table with my_v as shown below. *id and last_name are not usable:
INSERT INTO my_v (first_name, age)
VALUES ('Robert', 18), ('Mark', 40);
Then, 2 rows are inserted to person table as shown below:
postgres=# SELECT * FROM person;
id | first_name | last_name | age
----+------------+-----------+-----
1 | John | Smith | 27
2 | David | Miller | 32
| Robert | | 18
| Mark | | 40
(4 rows)
And, you can update person table with my_v as shown below. *id and last_name are not usable:
UPDATE my_v SET first_name = 'Tom' WHERE age = 32;
Then, person table is updated as shown below:
postgres=# SELECT * FROM person;
id | first_name | last_name | age
----+------------+-----------+-----
1 | John | Smith | 27
| Robert | | 18
| Mark | | 40
2 | Tom | Miller | 32
(4 rows)
And, you can delete 2 rows from person table with my_v as shown below. *id and last_name are not usable:
DELETE FROM my_v WHERE age = 18 OR age = 40;
Then, 2 rows are deleted from person table as shown below:
postgres=# SELECT * FROM person;
id | first_name | last_name | age
----+------------+-----------+-----
1 | John | Smith | 27
2 | Tom | Miller | 32
(2 rows)
And, you can create my_v view with raw values as shown below:
CREATE VIEW my_v AS
SELECT TEXT 'Hello', 'World';
*Memos:
-
TESTcan set the type and column name for'Hello'. - Unsetting
TESTgets the error. - You can set other type like
VARCHAR(20)for'Hello'.
Then, calling my_v gets the result as shown below:
postgres=# SELECT * FROM my_v;
text | ?column?
-------+----------
Hello | World
(1 row)
And, you can create my_v view with VALUES statement as shown below:
CREATE VIEW my_v AS
VALUES ('Hello', 'World');
Then, calling my_v gets the result as shown below:
postgres=# SELECT * FROM my_v;
column1 | column2
---------+---------
Hello | World
(1 row)
Top comments (0)