Re: join and dynamic view

> As soon as you or 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.

Ok, got it:
SELECT sid, sname, SUM("OPS") AS "OPS", SUM("MPD") AS "MPD" FROM (
SELECT staff.*,
CASE dsdesc WHEN 'OPS' THEN rrank ELSE 0 END AS "OPS",
CASE dsdesc WHEN 'MPD' THEN rrank ELSE 0 END AS "MPD"
FROM staff,depts,ranks WHERE sid=rsid AND did=rdid ) as foo
GROUP BY sid, sname ;

sid | sname | OPS | MPD
-----+---------+-----+-----
1 | Rod | 0 | 3
2 | Jayne | 5 | 2
3 | Freddie | 3 | 0
(3 rows)

Gary,
I'm going to write the plpgsql function to dynamically amend the view.
In the meantime you may think about creating a trigger which fires every

time a new department is entered and which calls the function then.

Regards, Christoph