Re: [SQL] join and dynamic view

[Tomasz Myrta]

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



---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html