Re: join and dynamic view

Hi Christoph, Tomasz,

Thanks to you both, I now have:

garytest=# select * from users;
sid | sname | ops | mpd
-----+---------+-----+-----
1 | Rod | | 3
2 | Jayne | 5 | 2
3 | Freddie | 3 |
(3 rows)

garytest=# insert into depts values ('A', 'ADM', 'Administrative');
INSERT 237559 1
garytest=# select * from users;
sid | sname | adm | mpd | ops
-----+---------+-----+-----+-----
1 | Rod | | 3 |
2 | Jayne | | 2 | 5
3 | Freddie | | | 3
(3 rows)

garytest=#

I found that the compile error complaining about the 'OR' was on the

CREATE OR REPLACE FUNCTION

line. I removed the 'OR REPLACE' and everything worked fine.

Also I had to change the returns to 'opaque' and 'return 0' to 'return null'

Thanks again

Gary

On Tuesday 17 Dec 2002 1:45 pm, Christoph Haller wrote:
> > I've now started amending your plpgsql script to create this, but as
>
> you can
>
> > see I've cocked up somewhere. I wonder if you could have a peek at it
>
> for
>
> > me.
>
> Gary,
>
> CREATE OR REPLACE FUNCTION
> create_users_view() returns integer as '
> DECLARE
> pg_views_rtype pg_views%ROWTYPE;
> vname_param TEXT;
> ranks_record RECORD;
> create_view TEXT;
> join_text TEXT;
> BEGIN
>
> vname_param:=''users'';
>
> SELECT INTO pg_views_rtype * FROM pg_views WHERE viewname = vname_param;
>
> IF FOUND THEN
> EXECUTE ''DROP VIEW '' || quote_ident(vname_param) ;
> END IF;
> create_view :=
> ''CREATE VIEW '' || quote_ident(vname_param) ||
> '' AS SELECT s.* '';
> join_text:='' from staff s '';
> FOR ranks_record IN
> EXECUTE ''SELECT did, dsdesc from depts ORDER BY did;''
> LOOP
> create_view :=
> create_view || '', '' || ranks_record.did ||
> ''.rrank AS '' || ranks_record.dsdesc;
> join_text :=
> join_text || '' left outer join ranks '' || ranks_record.did ||
> '' ON '' || ranks_record.did || ''.rsid = s.sid and '' ||
> ranks_record.did || ''.rdid = '' || quote_literal(ranks_record.did)
> ;
> END LOOP;
> create_view :=
> create_view || join_text || '';'';
> EXECUTE create_view ;
>
> RETURN 0;
> END;
> ' LANGUAGE 'plpgsql' ;
>
> should work.
>
> > I don't think it's good idea to do this, but you can recreate views
> > inside trigger on insert/update into depts.
>
> Tomasz,
> Could you please point out why this is not a good idea. Thanks.
>
> Regards, Christoph
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org

--
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