Thread: Support for ResultSetMetaData.getTableName

Looking through those bits of the archive that Google found, I see that
this has been discussed before, but there does seem to have been a
resolution.  As far as I can tell, even in the the
JDBC-4 development code, this has not been implemented yet (please tell
me I am wrong if I misread it).

There was talk about this needing "the new communications protocol", but
that was some years ago.  Are we still awaiting a new version of the
protocol that support this, or can it be implemented on what it there
now?  By now I am referring to 8.2, but if it is not there, can be it
added to 8.3?

Regards

David

On Tue, 21 Aug 2007, David Goodenough wrote:

> There was talk about this needing "the new communications protocol", but
> that was some years ago.  Are we still awaiting a new version of the
> protocol that support this, or can it be implemented on what it there
> now?  By now I am referring to 8.2, but if it is not there, can be it
> added to 8.3?
>

The discussion was focused on the ability of the server to return the
alias of a table as well as the base table name.  Because getColumnName
will return "b" for "SELECT a AS b FROM c AS d" we have decided that
getTableName should return "d" instead of "c".  Right now the server will
tell the driver about a, b, and c, but not d.  So we're sort of waiting
for the server to return "d", but no plans have been made to extend the
protocol to include this information at this point.  So you'll have to
keep waiting or use PGResultSetMetaData.getBaseTableName to return "c"
instead of "d".

Kris Jurka

Kris Jurka <books@ejurka.com> writes:
> The discussion was focused on the ability of the server to return the
> alias of a table as well as the base table name.  Because getColumnName
> will return "b" for "SELECT a AS b FROM c AS d" we have decided that
> getTableName should return "d" instead of "c".

[ itch... ]  Just looking at this again, I wonder whether that's going
in the wrong direction.  What is the point of getTableName, if not to
find out a table name that is usable in other queries?  I also notice
that the spec provides getSchemaName, which is sensible if one supposes
that the point of all this is to find out the actual table name, and is
completely nonsensical if the point is to find out the inherently
not-schema-qualified table alias.

I suggest that the following mapping might be more sensible:

    getColumnName        returns "a"
    getTableName        returns "c"
    getSchemaName        returns name of c's schema
    getColumnLabel        returns "b"

where the first three fail if the SELECT column isn't a simple column
reference, but getColumnLabel always works.

I'm not sure what the use-case is for finding out "d".

            regards, tom lane

On Mon, 27 Aug 2007, Tom Lane wrote:

> I suggest that the following mapping might be more sensible:
>
>     getColumnName        returns "a"
>     getTableName        returns "c"
>     getSchemaName        returns name of c's schema
>     getColumnLabel        returns "b"
>
> where the first three fail if the SELECT column isn't a simple column
> reference, but getColumnLabel always works.
>

I tried that back here:

http://archives.postgresql.org/pgsql-jdbc/2004-07/threads.php#00314

and continuing here:

http://archives.postgresql.org/pgsql-jdbc/2004-08/threads.php#00008

This message demonstrates that many other drivers do Column Name/Label in
a different way:

http://archives.postgresql.org/pgsql-jdbc/2004-08/msg00012.php

So while I agree with you that the above suggestions make sense from a
blank slate, we can't do this without causing problems.  It seems people
would like us to fallback from our position of table and schema returning
aliases, which while inconsistent, is certainly more useful at this point
in time.

Kris Jurka

On Monday 27 August 2007, Kris Jurka wrote:
> On Mon, 27 Aug 2007, Tom Lane wrote:
> > I suggest that the following mapping might be more sensible:
> >
> >     getColumnName        returns "a"
> >     getTableName        returns "c"
> >     getSchemaName        returns name of c's schema
> >     getColumnLabel        returns "b"
> >
> > where the first three fail if the SELECT column isn't a simple column
> > reference, but getColumnLabel always works.
>
> I tried that back here:
>
> http://archives.postgresql.org/pgsql-jdbc/2004-07/threads.php#00314
>
> and continuing here:
>
> http://archives.postgresql.org/pgsql-jdbc/2004-08/threads.php#00008
>
> This message demonstrates that many other drivers do Column Name/Label in
> a different way:
>
> http://archives.postgresql.org/pgsql-jdbc/2004-08/msg00012.php
>
> So while I agree with you that the above suggestions make sense from a
> blank slate, we can't do this without causing problems.  It seems people
> would like us to fallback from our position of table and schema returning
> aliases, which while inconsistent, is certainly more useful at this point
> in time.
>
> Kris Jurka

I have to admit that the thought of returning an alias name for a table
had not occurred to me, what I need is the c.

My need for it is using something like sql2java, when I have a constructed
bean for each record.  When I do a select that picks values from multiple
tables I would like to populate the beans with the relevant values from the
resultset.  I would like this to be generic code rather than having to tell
each bean which fields to pick.  For this purpose encoding the table from
which the bean comes in the bean (or its corresponding manager class) is easy,
the alias an an artifact of the select.

David