Re: Underlying view columns?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Fredrik Olsson <fredrik(dot)olsson(at)treyst(dot)se>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Underlying view columns?
Date: 2005-11-10 15:15:36
Message-ID: 8983.1131635736@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Fredrik Olsson <fredrik(dot)olsson(at)treyst(dot)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 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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Merlin Moncure 2005-11-10 15:16:37 Re: [HACKERS] win32 8.1 pgadmin dll issues
Previous Message Dave Page 2005-11-10 15:03:26 Re: [HACKERS] win32 8.1 pgadmin dll issues