Re: Change view with dependencies function: PG 8.3

 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@postgresql.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
>