Re: Views...

On Fri, 20 Apr 2001, Christian Marschalek wrote:

> Let's say I have 3 Tables... Teacher,Class,Pupil.
> Now if I want to have the lessions easily accessable I could make
> another Table called lessions with some attributes from
> Teacher,Class,Pupil combined.

CREATE TABLE Teach (
 tid serial not null primary key,
 teachname text not null,
 teachsalary float not null
);

CREATE TABLE Class (
 cid serial not null primary key,
 classtitle text not null,
 classcost float not null
);

CREATE TABLE Pupil (
 pid serial not null primary key,
 pupilname text not null,
);

If you want to show which pupil took which class with which instructor,
you could create a new table

CREATE TABLE Lessons (
 tid int references teach,
 cid int references class,
 pid int references pupil
);

and insert some data

However, getting information from Lessons isn't very pretty --
you just see the id numbers for classes, pupils, etc.

A view could create a joined version of this, letting you see more
information about the relationship of these tables.

CREATE VIEW lessons_view AS
SELECT  t.*,
        c.*,
        p.*
FROM    lessons l,
        teach t,
        pupil p,
        class c
WHERE   l.cid = c.cid
 AND    l.pid = p.pid
 AND    l.tid = t.pid;

Now, you can *treat* lessons_view as a table for SELECTs -- that is, you
can just select from it and get this nicer view of your data.

For extra credit, you can set it up so that you can insert/update/delete
from this view, and have this happen to the source tables. Read the
documentation on rules for more info.

Good luck and HTH,
--
Joel Burton   <jburton@scw.org>
Director of Information Systems, Support Center of Washington