Re: join and dynamic view

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