Re: Views dependency

["Shoaib Mir" <shoaibmir@xxxxxxxxx>]

Used a different way but finally got my dependent views and table info for a specific view. Here is how I did it:

==========================================
declare
pos integer;
wh integer;
tot integer;
diff integer;
outp varchar;
viewn varchar;
begin
    viewn := 'v2';
    select position('FROM' in definition) from pg_views where viewname = viewn into pos;
    select position('WHERE' in definition) from pg_views where viewname = viewn into wh;
    select length(definition) from pg_views where viewname = viewn into tot;

    diff := tot - pos;

    IF wh = 0 THEN
        select replace(substr(definition, pos+5, diff), ';', '') from pg_views where viewname = viewn into outp;
    END IF;

    IF wh != 0 THEN
        diff := wh - pos;
        diff := diff - 6;
        select substr(definition, pos+5, diff) from pg_views where viewname = viewn into outp;
    END IF;
end; 
=====================================

--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com
)

On 3/11/07, Shoaib Mir <shoaibmir@xxxxxxxxx> wrote:

I can see the following in pg_depend:

For view v1:

classid | objid  | objsubid | refclassid | refobjid | refobjsubid | deptype
---------+--------+----------+------------+----------+-------------+---------
    2618 | 153523 |        0 |       1259 |   153521 |           0 | n
    2618 | 153523 |        0 |       1259 |   153521 |           0 | i
    1247 | 153522 |        0 |       1259 |   153521 |           0 | i
    2618 | 153526 |        0 |       1259 |   153521 |           1 | n
    2618 | 153526 |        0 |       1259 |   153521 |           2 | n
    2618 | 153526 |        0 |       1259 |   153521 |           3 | n
    2618 | 153526 |        0 |       1259 |   153521 |           4 | n
    2618 | 153526 |        0 |       1259 |   153521 |           5 | n
    2618 | 153526 |        0 |       1259 |   153521 |           6 | n
    2618 | 153526 |        0 |       1259 |   153521 |           7 | n
    2618 | 153526 |        0 |       1259 |   153521 |           8 | n

For  v2:

classid | objid  | objsubid | refclassid | refobjid | refobjsubid | deptype
---------+--------+----------+------------+----------+-------------+---------
    2618 | 153526 |        0 |       1259 |   153524 |           0 | n
    2618 | 153526 |        0 |       1259 |   153524 |           0 | i
    1247 | 153525 |        0 |       1259 |   153524 |           0 | i

and in pg_rewrite I got two entries for v1 and v2 as:

rulename    | ev_class | ev_attr | ev_type | is_instead | ev_qual
---------+--------+----------+------------+----------+-------------+---------
_RETURN       |   153521 |      -1 | 1       | t          | <>
 _RETURN       |   153524 |      -1 | 1       | t          | <>
    
Right now confused on how do I actually get the dependent views from this kind of output... any ideas?

--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)

On 3/11/07, Alvaro Herrera < alvherre@xxxxxxxxxxxxxxxxx> wrote:

Shoaib Mir escribió:
> Suppose I have a view:
>
> create view v1 as select * from t1;
>
> and another view as:
>
> create view v2 as select * from v1;
>
> Now is there any query or any catalog table available by which I can get to
> know the dependency (names of views on which v2 depends) for view v2?

Yes, pg_depend has a row for v2 to indicate dependency on the pg_rewrite
row, which in turn has an entry to indicate dependency on v1.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.