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