Content-Type: text/plain;
  charset="us-ascii"
From: Gary Stainburn <gary.stainburn@ringways.co.uk>
Organization: Ringways Garages Ltd
To: pgsql-sql@postgresql.org
Subject: join and dynamic view
Date: Tue, 17 Dec 2002 10:58:40 +0000
User-Agent: KMail/1.4.3
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Message-Id: <200212171058.40706.gary.stainburn@ringways.co.uk>

Hi folks

is it possible to make a dynamically declare a view based on a table?

I have 3 tables

create table depts (
did	character unique not null, 	-- key
dsdesc	character (3),			-- short desc
ddesc	character varying(40)		-- long desc
);
create table staff (
sid	int4 not null unique,		-- key
sname	character varying(40),		-- name
);

create table ranks (
rsid	int4 not null references staff(sid),
rdid	character not null references depts(did),
rrank 	int4 not null,
primary key (rsid, rdid)
);

copy "depts" from stdin;
O	OPS	Operations
M	MPD	Motive Power Dept
\.
copy "staff" from stdin;
1	Rod
2	Jayne
3	Freddie
\.
copy "ranks" from stdin;
1	M	3
2	M	2
2	O	5
3	O	3
\.

Is it possible to now define a view such that it returns:

select * from myview;
sid  | Name    | OPS | MPD
-----+---------+-----+-----
 1   | Rod     |     |  3
 2   | Jayne   |  2  |  5
 3   | Freddie |  3  |

and if I add another row to depts, that the new row would be included?
-- 
Gary Stainburn
 
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000