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(at)scw(dot)org>
Director of Information Systems, Support Center of Washington