Message-ID: <3DFF2A7B.B26EEF93@rodos.fzk.de>
Date: Tue, 17 Dec 2002 14:45:31 +0100
From: Christoph Haller <ch@rodos.fzk.de>
MIME-Version: 1.0
To: gary.stainburn@ringways.co.uk
Cc: pgsql-sql@postgresql.org, jasiek@klaster.net
Subject: Re: join and dynamic view
Content-Type: text/plain; charset=iso-8859-2
Content-Transfer-Encoding: 7bit

>
> 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