From: Gary Stainburn <gary.stainburn@ringways.co.uk>
To: pgsql-sql@postgresql.org
Subject: join and dynamic view
Date: Tue, 17 Dec 2002 10:58:40 +0000
Message-ID: <200212171058.40706.gary.stainburn@ringways.co.uk> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gary.stainburn@ringways.co.uk
Subject: Re: join and dynamic view
In-Reply-To: <200212171058.40706.gary.stainburn@ringways.co.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox