Message-ID: <3DFF2875.6020709@klaster.net>
Date: Tue, 17 Dec 2002 14:36:53 +0100
From: Tomasz Myrta <jasiek@klaster.net>
User-Agent: Mozilla/5.0 (Windows; U; Win 9x 4.90; en-US;
	rv:1.2b) Gecko/20021016
MIME-Version: 1.0
To: Gary Stainburn <gary.stainburn@ringways.co.uk>
Cc: Christoph Haller <ch@rodos.fzk.de>, pgsql-sql@postgresql.org
Subject: Re: join and dynamic view
References: <3DFF1328.13B365D6@rodos.fzk.de>
	<200212171223.00034.gary.stainburn@ringways.co.uk>
In-Reply-To: <3DFF1328.13B365D6@rodos.fzk.de>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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.sid and 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 ranks as '' || 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