Fredrik Olsson <[EMAIL PROTECTED]> writes:
> Have I misunderstood the concept of pg_depend? Can it even be used for
> what I intend, and if not in what direction should I be searching next?
What you missed is that the per-column dependencies you are looking for
go from the view's rewrite rule to the underlying table.
Here's an example in CVS tip:
regression=# create table foo (f1 int, f2 text);
CREATE TABLE
regression=# create view bar as select * from foo;
CREATE VIEW
regression=# select
classid::regclass,objid,objsubid,refclassid::regclass,refobjid,refobjsubid,deptype
from pg_depend where refobjid in ('foo'::regclass,'bar'::regclass);
classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype
------------+-------+----------+------------+----------+-------------+---------
pg_type | 62950 | 0 | pg_class | 62949 | 0 | i
pg_class | 62951 | 0 | pg_class | 62949 | 0 | i
pg_type | 62955 | 0 | pg_class | 62954 | 0 | i
pg_rewrite | 62956 | 0 | pg_class | 62954 | 0 | i
pg_rewrite | 62956 | 0 | pg_class | 62949 | 1 | n
pg_rewrite | 62956 | 0 | pg_class | 62949 | 2 | n
pg_rewrite | 62956 | 0 | pg_class | 62954 | 0 | n
(7 rows)
What we have there is:
* implicit dependency of foo's rowtype on foo.
* implicit dependency of foo's toast table on foo.
* implicit dependency of bar's rowtype on bar.
* implicit dependency of bar's ON SELECT rewrite rule on bar.
* normal dependency of bar's rewrite rule on foo.f1 (refobjsubid is the
column number).
* normal dependency of bar's rewrite rule on foo.f2.
* normal dependency of bar's rewrite rule on foo as a whole.
That last dependency comes from the appearance of foo in bar's FROM
list, while the per-column dependencies come from the individual column
references in the SELECT output list.
There isn't anything in pg_depend that would let you associate
particular columns of bar's output with particular dependencies, so I'm
not sure it really will help for your problem. I don't think there's
any way you could find that out except by parsing the stored rule
expression, which I would strongly NOT recommend, as your code will
inevitably break every time we modify expression trees (which is often).
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
