Обсуждение: Views dependency
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?
I did check the pg_views but cant seem to get a simple output from it which should be like this:
view_name | dependent_views
v1 |
v2 | v1
--
Shoaib Mir
EnterpriseDB ( www.enterprisedb.com)
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?
I did check the pg_views but cant seem to get a simple output from it which should be like this:
view_name | dependent_views
v1 |
v2 | v1
--
Shoaib Mir
EnterpriseDB ( www.enterprisedb.com)
"Shoaib Mir" <shoaibmir@gmail.com> writes: > 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? If you get up-close-n-personal with pg_depend you can determine that. regards, tom lane
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.
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)
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@commandprompt.com> 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.
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 )
==========================================
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@gmail.com> 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@commandprompt.com> 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.