Re: REPOST: How to determine the table a query or a views columns come from?

On Tue, Mar 07, 2006 at 10:38:29AM +0000, Frank Church wrote:
> Quoting Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
>
> Can this info be obtained by querying the system tables,
> especially in the case of views? I am using 'scripting' languages and using C
> will be quite awkward.

Well, Tom suggests the PQftable() and PQftablecol() functions which are
exported by libpq and should be available in your favourite scripting
language whenever you execute the query. If not, you should probably
fix that.

If you really feel like digging through the catalogs you should
probably start with pg_rewrite. If you look carefully, under the
targetlist node there are a number of TARGETENTRY nodes. Within that
there are the fields "resorigtbl" and "resorigcol".

Note this is going straight through compatability and out the other
side. There is no guarentee that any of these fields exist in any
version, I'm just looking at 7.4.7 here.

Hope this helps,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.