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