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