From: Christoph Haller <ch@rodos.fzk.de>
To: gary.stainburn@ringways.co.uk
Cc: pgsql-sql@postgresql.org
Subject: Re: join and dynamic view
Date: Tue, 17 Dec 2002 14:06:55 +0100
Message-ID: <3DFF216E.32DACAB2@rodos.fzk.de> (raw)
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ch@rodos.fzk.de, gary.stainburn@ringways.co.uk
Subject: Re: join and dynamic view
In-Reply-To: <3DFF216E.32DACAB2@rodos.fzk.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox