Обсуждение: How to determine the table a query or a views columns come from?

Is there way to determine the table a query or a view's columns come from?

I am looking for something like that in postgresql. If it doesn't exist is there
some generalized SQL that can parse the query or view's definition and retrieve
the list.

something like

column name | actual column name | source table | actual source table

column name is either the original name or an alias for it, and so is source
table.

Frank Church <pgsql@adontendev.net> writes:
> Is there way to determine the table a query or a view's columns come from?

Yeah, there's some support for that in the protocol.  libpq exposes it
as PQftable() and PQftablecol().

            regards, tom lane

Quoting Tom Lane <tgl@sss.pgh.pa.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.

I have got to find if libpq's output is exposed in PHP or Ruby.

> Frank Church <pgsql@adontendev.net> writes:
> > Is there way to determine the table a query or a view's columns come from?
>
> Yeah, there's some support for that in the protocol.  libpq exposes it
> as PQftable() and PQftablecol().
>
>             regards, tom lane
>

Frank Church wrote:
> Quoting Tom Lane <tgl@sss.pgh.pa.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.
>
> I have got to find if libpq's output is exposed in PHP or Ruby.
>
>
>>Frank Church <pgsql@adontendev.net> writes:
>>
>>>Is there way to determine the table a query or a view's columns come from?
>>
>>Yeah, there's some support for that in the protocol.  libpq exposes it
>>as PQftable() and PQftablecol().

Do you need this?

test=# \d x
               View "public.x"
  Column |         Type          | Modifiers
--------+-----------------------+-----------
  name   | character varying(50) |
View definition:
  SELECT questions.name
    FROM questions;


If you need to see what query "\d x" is running, start psql with -E and
it will show it.

--
Postgresql & php tutorials
http://www.designmagick.com/

Quoting Tom Lane <tgl@sss.pgh.pa.us>:

Quoting Tom Lane <tgl@sss.pgh.pa.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.

> Frank Church <pgsql@adontendev.net> writes:
> > Is there way to determine the table a query or a view's columns come from?
>
> Yeah, there's some support for that in the protocol.  libpq exposes it
> as PQftable() and PQftablecol().
>
>             regards, tom lane
>

On Tue, Mar 07, 2006 at 10:38:29AM +0000, Frank Church wrote:
> Quoting Tom Lane <tgl@sss.pgh.pa.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@svana.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.