Re: join and dynamic view

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