Re: Underlying view columns?

Fredrik Olsson <fredrik.olsson@treyst.se> 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
whererefobjid in ('foo'::regclass,'bar'::regclass); classid   | objid | objsubid | refclassid | refobjid | refobjsubid
|deptype
 
------------+-------+----------+------------+----------+-------------+---------pg_type    | 62950 |        0 | pg_class
 |    62949 |           0 | ipg_class   | 62951 |        0 | pg_class   |    62949 |           0 | ipg_type    | 62955
|       0 | pg_class   |    62954 |           0 | ipg_rewrite | 62956 |        0 | pg_class   |    62954 |           0
|ipg_rewrite | 62956 |        0 | pg_class   |    62949 |           1 | npg_rewrite | 62956 |        0 | pg_class   |
62949 |           2 | npg_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