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
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 |