Re: join and dynamic view

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