Re: join and dynamic view - Mailing list pgsql-sql

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