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