Re: join and dynamic view

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