Gary Stainburn wrote:
> How could a plpgsql dynamically create the view?
> How about a trigger from the on-update of the depts table to drop the
> view and
> then create a new one. Could it not do the same thing using outer joins.
I don't think it's good idead to do this, but you can recreate views
inside trigger on insert/update into depts. It would look like this (it
has a lot of errors ;-) ):
We have to change this query into dynamical plpgsql: select sid,sname ,ranks_ops.rrank as ops --!!! column names
!!! ,ranks_mpd.rrank as mpd ... from staff s left join ranks as ranks_ops --!!! joins !!! on
(s.sid=ranks_ops.sidand ranks_ops.rdid='O') left join ranks as ranks_ops on (s.sid=ranks_ops.sid and
ranks_ops.rdid='M') ...
Here is the solution:
CREATE OR REPLACE FUNCTION after_depts_change() RETURNS opaque AS '
DECLARE table_alias varchar; column_names varchar; joins varchar; x RECORD;
BEGIN column_names=''; joins=''; for x in select * from depts loop table_alias=''ranks_'' || x.dsdesc;
column_names=column_names|| '','' || table_alias || ''.rrank as '' || x.dsdesc; joins=joins || ''left join
ranksas '' || table_alias || '' on (s.sid='' || table_alias || ''.sid and " || table_alias || ''.rdid=''''
||x.did || '''') ''; end loop; execute ''drop view myview; create view myview as select sid,sname'' || column_names
||'' from staff s '' || joins; RETURN NEW;
END;
' LANGUAGE 'plpgsql';
DROP TRIGGER depts_change on depts;
CREATE TRIGGER depts_change AFTER insert or update or delete on depts for each row execute procedure
after_depts_change();
Tomasz Myrta
