Customize database queries using views in PostgreSQL

by Contributor Melonfire | Jan 27, 2006 4:07:00 PM

Tags: Databases, database, Contributor Melonfire, PostgreSQL

Takeaway: One of PostgreSQL's more useful capabilities is the ability to create custom views. This document will show you how to create, use and delete views.

One of PostgreSQL's more useful capabilities is the ability to create custom "views". These views are nothing more than pre-defined SQL queries, which are stored in the database and can be re-used as and when needed. Storing frequently-used SQL queries in this manner is more efficient than typing them out each time and also offers greater flexibility, because the data set generated by a view can itself be manipulated using SQL.

This document will show you how to create, use and delete views in PostgreSQL.

More easily copy and paste versions of Listings A-E are included in the text file included in the downloadable Zip file.

Sample tables

Begin by creating three sample tables, using the SQL commands below:

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 commands above create three tables: one for story titles, one for author names, and one mapping stories to authors. Populate these tables with some sample records. (Listing A)

Listing A

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, let's suppose we want to obtain a comprehensive report of available stories and their authors. This is best accomplished by joining the three tables on their common fields, as shown in Listing B.

Listing B

Naturally, it's inefficient to type out such a long query over and over again. Therefore, it makes sense to save this query as a view. Here's how (Listing C).

Listing C

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 to create a view is CREATE VIEW name AS query. This will save the query string query to the database under the name name. You can verify this by checking the output of the \dvcommand, as below:

To re-use a view, run a SELECT query on it, as though it were a normal table. Listing D shows you how.

Listing D

As Listing D illustrates, SELECT-ing from a view actually executes the original saved query. Naturally, you can further manipulate the output of a view by using SQL modifiers with the SELECT statement. For example, Listing E returns the top three stories only.

Listing E

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

You can delete a view with the DROP VIEW name command, where name is the name of the view to be erased. Here's an example:

test=# DROP VIEW myview;

Verify that the view no longer exists by checking the output of the \dv command:

test=# \dv
No relations found.

As the above examples illustrate, views provide a convenient shortcut to oft-used SELECT queries, and also makes it easy to obtain different perspectives of the same data. Try them out yourself.

5 comment(s)