From: Christoph Haller <ch@rodos.fzk.de>
To: gary.stainburn@ringways.co.uk
Cc: pgsql-sql@postgresql.org
Subject: Re: join and dynamic view
Date: Tue, 17 Dec 2002 13:06:01 +0100
Message-ID: <3DFF1328.13B365D6@rodos.fzk.de> (raw)
>
> is it possible to make a dynamically declare a view based on a table?
>
Yes, by all means.
>
> 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?
>
^^^^^^^^^^^^^^^^ you mean column, don't you?
The closest query I can get so far is
SELECT staff.*,
CASE dsdesc WHEN 'OPS' THEN rrank ELSE NULL END AS "OPS",
CASE dsdesc WHEN 'MPD' THEN rrank ELSE NULL END AS "MPD"
FROM staff,depts,ranks WHERE sid=rsid AND did=rdid ;
sid | sname | OPS | MPD
-----+---------+-----+-----
1 | Rod | | 3
2 | Jayne | | 2
2 | Jayne | 5 |
3 | Freddie | 3 |
(4 rows)
but
sid | sname | OPS | MPD
-----+---------+-----+-----
1 | Rod | | 3
2 | Jayne | 5| 2
3 | Freddie | 3 |
(3 rows)
is what you want (I suppose Jayne's 2 in OPS and 5 in MPD is a mismatch
of yours).
As soon as you are somebody else can tell me how to merge Jayne's two
rows into one,
I'm sure I can write a plpgsql function to dynamically create the view
you're looking for.
Regards, Christoph
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: ch@rodos.fzk.de, gary.stainburn@ringways.co.uk
Subject: Re: join and dynamic view
In-Reply-To: <3DFF1328.13B365D6@rodos.fzk.de>
* 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