Tutorial - Creating and Using Views

Tutorial - Creating and Using Views  

What are views? Views are simple ways of running and outputting selected data from complex queries in your database.

Why are they useful? They save a lot of typing and effort, and present only the data we want.

PostgreSQL uses views for some of it's information.

We'll use the same tables and information from the Referential Integrity introduction.

CREATE TABLE client (

clientid SERIAL NOT NULL PRIMARY KEY,

clientname VARCHAR(255)

CREATE TABLE clientcontact (

contactid SERIAL NOT NULL PRIMARY KEY,

clientid int CONSTRAINT client_contact_check REFERENCES client(clientid),

name VARCHAR(255),

phone VARCHAR(255),

fax VARCHAR(255),

emailaddress VARCHAR(255)

Connect to your database:

tomcat@elrond ~$ psql -d tutorial

First we'll have a look at the system tables (below is only a sample selection):

tutorial=> dS

List of relations

Name | Type | Owner

pg_index | table | postgres

pg_indexes | view | postgres

pg_log | special | postgres

pg_tables | view | postgres

So the system information is made up of tables, views, and 'special' relation types.

We'll have a look at the 'pg_indexes' view and see what it does:

tutorial=> d pg_indexes

View 'pg_indexes'

Attribute | Type | Modifier

tablename | name |

indexname | name |

indexdef | text |

View definition: SELECT c.relname AS tablename, i.relname AS indexname, pg_get_indexdef(x.indexrelid) AS indexdef FROM pg_index

x, pg_class c, pg_class i WHERE ((((c.relkind = 'r'::'char') AND (i.relkind = 'i'::'char')) AND (c.oid =

x.indrelid)) AND (i.oid = x.indexrelid));

So pg_indexes is actually a 'view' running a fairly complex query. When you run a query against a view, PostgreSQL

automatically changes the query and runs it against the 'View Definition' (which of course is the actual query that is

run).

If we did a query against the pg_indexes view then ran the query with the 'View Definition', we'd get the same

result(s):

tutorial=> SELECT * FROM pg_indexes WHERE tablename LIKE 'client';

tablename | indexname | indexdef

client | client_pkey | CREATE UNIQUE INDEX client_pkey ON client USING btree (clientid int4_ops)

(1 row)

tutorial=> SELECT c.relname AS tablename, i.relname AS indexname, pg_get_indexdef(x.indexrelid) AS indexdef FROM pg_index x,

pg_class c, pg_class i WHERE ((((c.relkind = 'r'::'char') AND (i.relkind = 'i'::'char')) AND (c.oid =

x.indrelid)) AND (i.oid = x.indexrelid) AND (c.relname LIKE 'client'));

tablename | indexname | indexdef

client | client_pkey | CREATE UNIQUE INDEX client_pkey ON client USING btree (clientid int4_ops)

(1 row)

Exactly the same results. Which is easier? Obviously using the view. The time and effort taken to run the query against the view

and the query are exactly the same, there's no disadvantage to using a view as opposed to a proper query (translating these views

to other databases might cause a few problems depending on their support for using them).

Now we'll construct the query that we want to turn into a view. For this introduction, we'll be simply joining the two tables on

their primary keys, but obviously we can make these as complex as we need (as above).

We'll start off showing how to construct a basic joining query. First, look at all of the data:

tutorial=> SELECT * FROM client, clientcontact;

clientid | clientname | contactid | clientid | name | phone | fax | emailaddress

1 | Client 1 | 1 | 1 | Chris | 12345 | 67890 | tomcat@designmagick.com

1 | Client 1 | 2 | 1 | Chris | 23456 | 78901 | chris@designmagick.com

2 | Client 2 | 1 | 1 | Chris | 12345 | 67890 | tomcat@designmagick.com

2 | Client 2 | 2 | 1 | Chris | 23456 | 78901 | chris@designmagick.com

(4 rows)

This isn't what we want, since the data is duplicated, notice there are 2 clients (only 2 unique clientid's), and 2 contacts (2

unique contactid's), but we're getting 4 rows.

Since the clientid field is in both tables, we can use that to join the tables:

tutorial=> SELECT * FROM client, clientcontact WHERE client.clientid = clientcontact.clientid;

clientid | clientname | contactid | clientid | name | phone | fax | emailaddress

1 | Client 1 | 5 | 1 | Chris | 12345 | 67890 | tomcat@designmagick.com

1 | Client 1 | 6 | 1 | Chris | 23456 | 78901 | chris@designmagick.com

(2 rows)

We've got the right data now (since in our examples we only created 2 contact's for the first client). Now we have the data we

want, we can cut our query down to only the columns we need. In this example, we'll only show the client's name, the contact name

and email address for that client, so these fields:

clientid

clientname

(contact) name

emailaddress

So our query becomes:

tutorial=> SELECT clientid, clientname, name, emailaddress FROM client, clientcontact WHERE client.clientid =

clientcontact.clientid;

but we get an error:

ERROR: Column reference 'clientid' is ambiguous

So we'll have to change our query further so we don't get ambiguous information (which means PostgreSQL doesn't know which

clientid to pick since it's in both tables - we'll pick the clientid from the client table).

tutorial=> SELECT client.clientid, clientname, name, emailaddress FROM client, clientcontact WHERE client.clientid =

clientcontact.clientid;

clientid | clientname | name | emailaddress

1 | Client 1 | Chris | tomcat@designmagick.com

1 | Client 1 | Chris | chris@designmagick.com

(2 rows)

Now we have only the information we want to use in the view.

Now we'll create the view:

tutorial=> CREATE VIEW client_contact_list AS SELECT client.clientid, clientname, name, emailaddress FROM client,

clientcontact WHERE client.clientid = clientcontact.clientid;

CREATE

The syntax for creating a view is pretty simple:

CREATE VIEW <view_name> AS <select query>;

So once we know what the query is we want to run, we can turn it into a view.

To prove the creation worked, show the list of relations present in the database:

tutorial=> d

List of relations

Name | Type | Owner

client | table | tomcat

client_clientid_seq | sequence | tomcat

client_contact_list | view | tomcat

clientcontact | table | tomcat

clientcontact_contactid_seq | sequence | tomcat

(5 rows)

Now that we have to view, we can simply:

tutorial=> SELECT * FROM client_contact_list;

clientid | clientname | name | emailaddress

1 | Client 1 | Chris | tomcat@designmagick.com

1 | Client 1 | Chris | chris@designmagick.com

(2 rows)

We can also pass a regular query clause onto the view, so we could for example, select only contact email addresses start with

'C':

tutorial=> SELECT * FROM client_contact_list WHERE emailaddress ILIKE 'c%';

clientid | clientname | name | emailaddress

1 | Client 1 | Chris | chris@designmagick.com

(1 row)

Which is the same result as using the original query (with the extra WHERE clause):

tutorial=> SELECT client.clientid, clientname, name, emailaddress FROM client, clientcontact WHERE client.clientid =

clientcontact.clientid AND emailaddress ILIKE 'c%';

clientid | clientname | name | emailaddress

1 | Client 1 | Chris | chris@designmagick.com

(1 row)

The whole point of the view is that it makes it really easy to return the data you want from complex queries.

If you don't want the view anymore you can of course, remove it:

DROP VIEW <view_name>;

It is of course recommended you only do this on views that you have created, and not the system views.

That ends the basic introduction to using views. They're a very easy way to get only the data you want from running complex

queries.