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 ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
