Re: Change view with dependencies function: PG 8.3

From: Sim Zacks <sim(at)compulab(dot)co(dot)il>
To: Thangalin <thangalin(at)gmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Change view with dependencies function: PG 8.3
Date: 2010-09-12 06:50:42
Message-ID: 4C8C7842.9020803@compulab.co.il
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Wow. I wrote that function 5 years ago.
Go archives!!

One possibility is you can't use double quotes (") except for an
identifier. You have to surround it with single quotes. If you have
single quotes within the view definition (as you do) you have to make
those 2 single quotes ('')

As far as I know there is no other way of doing it: I just tried it on
8.3.5 and it worked fine. Here is an example:
create table tbl_employees(employeeid int serial primary key, firstname
text, lastname text, idnumber text);
create or replace view view1 as select employeeid,firstname,lastname
from tbl_employees;
create or replace view view2 as select * from view1;
create or replace view view3 as select firstname from view2;
create or replace function f1(eid int) returns text as
$$
declare
lname text;
begin
select lastname into lname from view2 where employeeid=eid;
return lname;
end;
$$ language 'plpgsql';

select * from viewchanger('view1','create or replace view view1 as
select employeeid,firstname,lastname,idnumber from tbl_employees')
It returns all the views and functions that were regenerated:
view1
view2
view3
f1

If that doesn't work, this function shows all the code that the
viewchanger is supposed to run.
If it doesn't help you, post the results of select * from
changesneeded(viewname,viewdef):

create or replace function changesneeded(viewname text, viewdefinition text)
returns setof text as
$$
declare
viewnames text[];
viewdefs text[];
i int;
row record;
rowproc record;

begin
viewnames=ARRAY[viewname];
viewdefs=ARRAY[viewdefinition];
i=array_lower(viewnames, 1);
Loop

for row in select distinct c.relname,e.definition from pg_class d,
pg_depend a join pg_rewrite b on a.objid=b.oid
join pg_class c on ev_class=c.oid
join pg_views e on e.viewname=c.relname
where refclassid = 'pg_class'::regclass and refobjid = d.oid
and ev_class<>d.oid and d.relname=viewnames[i]
Loop
if row.relname is not null then
viewnames[array_upper(viewnames, 1)+1]=row.relname;
viewdefs[array_upper(viewdefs, 1)+1]='Create or Replace View
' ||
row.relname || ' as ' || row.definition;
end if;
end loop;
i:=i+1;
if viewnames[i] is null then
exit;
end if;
end loop;
return next 'drop view ' || viewname || ' cascade';
for i in array_lower(viewdefs, 1)..array_upper(viewdefs, 1) loop
for rowproc in select proname from pg_proc where prosrc like '%' ||
viewnames[i] || '%'
loop
viewnames[array_upper(viewnames, 1)+1]=rowproc.proname;
viewdefs[array_upper(viewdefs,
1)+1]=rebuildfunction(rowproc.proname);
end loop;
end loop;
for i in array_lower(viewdefs, 1)..array_upper(viewdefs, 1) loop
return next viewdefs[i];
end loop;
return;
end;
$$ language 'plpgsql';

On 10-Sep-2010 6:14 PM, Thangalin wrote:
> Hi,
>
> Found Sim's functions:
>
> http://www.mail-archive.com/pgsql-general(at)postgresql(dot)org/msg57733.html
>
> How do you run the viewchanger function? I have tried:
>
> SELECT 1 FROM viewchanger( 'beplanallcover',
> "CREATE OR REPLACE VIEW beplanallcover AS
> SELECT DISTINCT p.personid, ... WHERE p.personid='1234'" )
>
> The error message is:
>
> ERROR: function CREATE OR REPLACE VIEW beplanallcover AS
> SELECT DISTINCT p.pe <http://p.pe>() does not exist
> HINT: No function matches the given name and argument types. You may
> need to add explicit type casts.
>
> I do not know why *p.personid* is being changed to *p.pe <http://p.pe>()*.
>
> I could not find much information on whether 8.4 provides a native
> facility to change a VIEW's underlying query statement without having
> to drop all dependencies. (I noticed there is an ALTER VIEW and
> mechanism to add columns.) Any information on other solutions would be
> greatly appreciated.
>
> Thank you!
> Dave
>

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Lew 2010-09-12 14:44:16 Re: Getting The Last Entry
Previous Message Lacey Powers 2010-09-12 02:59:21 Re: pgdg84 repository