Custom PostgreSQL database query with views

Welcome to the Oracle community forum and interact with 2 million technical staff. One of the main features of PostgreSQL is to create custom "views". These views are only pre-defined SQL queries, they are stored in the database and can be reused as needed. Therefore, it is more effective to store frequently-used SQL queries in this way than to input them manually each time.

Welcome to the Oracle community forum and interact with 2 million technical staff> A major feature of PostgreSQL is to create custom "views", which are only pre-defined SQL queries, they are stored in the database and can be reused as needed. Therefore, it is more effective to store frequently-used SQL queries in this way than to input them manually each time.

Welcome to the Oracle community forum and interact with 2 million technical staff> enter

A major feature of PostgreSQL is to create custom "views". These views are only pre-defined SQL queries, which are stored in the database and can be reused as needed. Therefore, saving frequently-used SQL queries in this way is more efficient and flexible than inputting data manually each time, because the dataset generated by the view itself can be operated through SQL.

This article describes how to create, use, and delete views in a PostgreSQL database.

Example Table

Use the following SQL command to create three sample tables:

test=# CREATE TABLE stories (id INT, title VARCHAR, time TIMESTAMP);test=# CREATE TABLE authors (id INT, name VARCHAR);test=# CREATE TABLE stories_authors_link (story INT, author INT);

The preceding command creates three tables: one for the title of the novel, the other for the name of the author, and the other for the ing between the author and the novel. Use the code in list A to fill in records in the table:

List:

test=# INSERT INTO authors VALUES (1, 'John Doe');test=# INSERT INTO authors VALUES (2, 'James White');test=# INSERT INTO authors VALUES (3, 'Ellen Sue');test=# INSERT INTO authors VALUES (4, 'Gina Haggelstrom');test=# INSERT INTO authors VALUES (5, 'Jane Ki');test=# INSERT INTO stories VALUES (100, 'All Tied Up', '2005-04-01 12:37:00');test=# INSERT INTO stories VALUES (112, 'Into Thin Air...', '2005-04-02 06:54:12');test=# INSERT INTO stories VALUES (127, 'The Oxford Blues', '2005-06-12 18:01:43');test=# INSERT INTO stories VALUES (128, 'Crash!', '2005-03-27 09:12:17');test=# INSERT INTO stories VALUES (276, 'Memories Of Malgudi', '2005-06-09 23:35:57');test=# INSERT INTO stories VALUES (289, 'The Big Surprise', '2005-05-30 08:21:02');test=# INSERT INTO stories VALUES (301, 'Indians and The Cowboy', '2005-04-16 11:19:28');test=# INSERT INTO stories_authors_link VALUES (112, 2);test=# INSERT INTO stories_authors_link VALUES (127, 1);test=# INSERT INTO stories_authors_link VALUES (128, 5);test=# INSERT INTO stories_authors_link VALUES (276, 5);test=# INSERT INTO stories_authors_link VALUES (289, 3);test=# INSERT INTO stories_authors_link VALUES (301, 5);test=# INSERT INTO stories_authors_link VALUES (100, 1);

Next, assume that we want to obtain a complete report about the novel and its authors. This is best done by connecting the public fields of the three tables, as shown in List B:

List B:

test=# SELECT s.title, a.name, s.timetest-# FROM stories AS s, authors AS a, stories_authors_link AS satest-# WHERE s.id = sa.storytest-# AND a.id = sa.authortest-# ORDER BY s.timetest-# DESC;title|name|time------------------------+-------------+---------------------The Oxford Blues| John Doe| 2005-06-12 18:01:43Memories Of Malgudi| Jane Ki| 2005-06-09 23:35:57The Big Surprise| Ellen Sue| 2005-05-30 08:21:02Indians and The Cowboy | Jane Ki| 2005-04-16 11:19:28Into Thin Air...| James White | 2005-04-02 06:54:12All Tied Up| John Doe| 2005-04-01 12:37:00Crash!| Jane Ki| 2005-03-27 09:12:17(7 rows)

Obviously, if you enter such a long query, It is very invalid. Therefore, it makes sense to store the query as a view. You can do this:

test=# CREATE VIEW myview AS SELECT s.title, a.name, s.time FROM stories AS s, authors AS a, stories_authors_link AS sa WHERE s.id = sa.story AND a.id = sa.author ORDER BY s.time DESC;

The syntax for creating a VIEW is create view name AS query, which stores query strings in the database with name. You can run the dv command to check the output, AS shown below:

test=# dv List of relations Schema | Name | Type | Owner--------+--------+------+------- public | myview | view | pgsql(1 row)

If you want to reuse a view, you can run a SELECT query, just like a normal table, as shown in list C:

List C:

test=# SELECT * FROM myview;title|name|time------------------------+-------------+-------------The Oxford Blues| John Doe| 2005-06-12 18:01:43Memories Of Malgudi| Jane Ki| 2005-06-09 23:35:57The Big Surprise| Ellen Sue| 2005-05-30 08:21:02Indians and The Cowboy | Jane Ki| 2005-04-16 11:19:28Into Thin Air...| James White | 2005-04-02 06:54:12All Tied Up| John Doe| 2005-04-01 12:37:00Crash!| Jane Ki| 2005-03-27 09:12:17(7 rows)

As shown in list C: Selecting from a view actually runs the original storage query. Naturally, you can use the SQL operator in the SELECT statement to operate on the output of a view, see the example in list D.

List D:

test=# SELECT title, name FROM myview LIMIT 3;title|name---------------------+-----------The Oxford Blues| John DoeMemories Of Malgudi | Jane KiThe Big Surprise| Ellen Sue(3 rows)

To verify that the original view does not exist, run the dv command output to check whether the view exists:

test=# dvNo relations found.

Note: similar to the preceding example, the view provides a simple and quick way to complete SELECT queries that are frequently used, and you can easily obtain different perspectives of the same data.