From: Gary Stainburn <gary.stainburn@ringways.co.uk>
To: Christoph Haller <ch@rodos.fzk.de>
Cc: pgsql-sql@postgresql.org
Subject: Re: join and dynamic view
Date: Tue, 17 Dec 2002 12:23:00 +0000
Message-ID: <200212171223.00034.gary.stainburn@ringways.co.uk> (raw)
In-Reply-To: <3DFF1328.13B365D6@rodos.fzk.de>
References: <3DFF1328.13B365D6@rodos.fzk.de>
Hi Christoph,
On Tuesday 17 Dec 2002 12:06 pm, Christoph Haller wrote:
> > 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?
What I mean here was that if I add another row to the depts table, e.g.
A ADM Administrative
I would like the ADM column to automatically appear in the 'myview' view
without having to recreate the view - i.e. the rows in the 'depts' table
become columns in 'myview' view
> 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 ;
Surely the problem with this is that I'd have to drop/amend/create the view
every time I add a row to 'depts'. Couldn't I just do that using an outer
join instead of a case?
>
> 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).
Yes it was, sorry.
> 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.
How could a plpgsql dynamically create the view?
How about a trigger from the on-update of the depts table to drop the view and
then create a new one. Could it not do the same thing using outer joins.
(I've done VERY little plpgsql and even less with triggers.
>
> Regards, Christoph
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
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: gary.stainburn@ringways.co.uk, ch@rodos.fzk.de
Subject: Re: join and dynamic view
In-Reply-To: <200212171223.00034.gary.stainburn@ringways.co.uk>
* 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