From: Christoph Haller <ch@rodos.fzk.de>
To: gary.stainburn@ringways.co.uk
Cc: pgsql-sql@postgresql.org
Cc: jasiek@klaster.net
Subject: Re: join and dynamic view
Date: Tue, 17 Dec 2002 14:45:31 +0100
Message-ID: <3DFF2A7B.B26EEF93@rodos.fzk.de> (raw)
>
> I've now started amending your plpgsql script to create this, but as
you can
> see I've cocked up somewhere. I wonder if you could have a peek at it
for
> me.
>
Gary,
CREATE OR REPLACE FUNCTION
create_users_view() returns integer as '
DECLARE
pg_views_rtype pg_views%ROWTYPE;
vname_param TEXT;
ranks_record RECORD;
create_view TEXT;
join_text TEXT;
BEGIN
vname_param:=''users'';
SELECT INTO pg_views_rtype * FROM pg_views WHERE viewname = vname_param;
IF FOUND THEN
EXECUTE ''DROP VIEW '' || quote_ident(vname_param) ;
END IF;
create_view :=
''CREATE VIEW '' || quote_ident(vname_param) ||
'' AS SELECT s.* '';
join_text:='' from staff s '';
FOR ranks_record IN
EXECUTE ''SELECT did, dsdesc from depts ORDER BY did;''
LOOP
create_view :=
create_view || '', '' || ranks_record.did ||
''.rrank AS '' || ranks_record.dsdesc;
join_text :=
join_text || '' left outer join ranks '' || ranks_record.did ||
'' ON '' || ranks_record.did || ''.rsid = s.sid and '' ||
ranks_record.did || ''.rdid = '' || quote_literal(ranks_record.did)
;
END LOOP;
create_view :=
create_view || join_text || '';'';
EXECUTE create_view ;
RETURN 0;
END;
' LANGUAGE 'plpgsql' ;
should work.
> I don't think it's good idea to do this, but you can recreate views
> inside trigger on insert/update into depts.
Tomasz,
Could you please point out why this is not a good idea. Thanks.
Regards, Christoph
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: ch@rodos.fzk.de, gary.stainburn@ringways.co.uk, jasiek@klaster.net
Subject: Re: join and dynamic view
In-Reply-To: <3DFF2A7B.B26EEF93@rodos.fzk.de>
* 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