Message-ID: <3DFF1328.13B365D6@rodos.fzk.de>
Date: Tue, 17 Dec 2002 13:06:01 +0100
From: Christoph Haller <ch@rodos.fzk.de>
MIME-Version: 1.0
To: gary.stainburn@ringways.co.uk
Cc: pgsql-sql@postgresql.org
Subject: Re: join and dynamic view
Content-Type: text/plain; charset=iso-8859-2
Content-Transfer-Encoding: 7bit

>
> 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