Message-ID: <3DFF216E.32DACAB2@rodos.fzk.de>
Date: Tue, 17 Dec 2002 14:06:55 +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

> 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