From: Tomasz Myrta <jasiek@klaster.net>
To: Gary Stainburn <gary.stainburn@ringways.co.uk>
Cc: Christoph Haller <ch@rodos.fzk.de>
Cc: pgsql-sql@postgresql.org
Subject: Re: join and dynamic view
Date: Tue, 17 Dec 2002 14:36:53 +0100
Message-ID: <3DFF2875.6020709@klaster.net> (raw)
In-Reply-To: <3DFF1328.13B365D6@rodos.fzk.de>
References: <3DFF1328.13B365D6@rodos.fzk.de>
<200212171223.00034.gary.stainburn@ringways.co.uk>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jasiek@klaster.net, gary.stainburn@ringways.co.uk, ch@rodos.fzk.de
Subject: Re: join and dynamic view
In-Reply-To: <3DFF2875.6020709@klaster.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox