Skip site navigation (1) Skip section navigation (2)

Re: Support for DatabaseMetadata: getCatalogName, getTableName,

From: Ken Johanson <pg-user(at)kensystem(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-jdbc(at)postgresql(dot)org, Kris Jurka <books(at)ejurka(dot)com>
Subject: Re: Support for DatabaseMetadata: getCatalogName, getTableName,
Date: 2007-01-05 07:16:46
Message-ID: 459DFB5E.9030701@kensystem.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
Tom Lane wrote:
>>> The previous discussion I cited was referring to getColumnName and how 
>>> it really should be referring to the alias of a result, not the column 
>>> itself.  That is for "SELECT a AS b FROM c AS d" should return "b" for 
>>> getColumnName(1).  If we accept that as true then it follows that 
>>> getTableName(1) should return "d", not "c".
> 
>> Understood now, thanks. I agree/vote this is the best spec 
>> interpretation/behavior (for its lack of a 'getTableLabel').
> 
> Urm ... I might agree with that, except for its signal lack of usefulness.
> What is the point of knowing the table alias?  The
> underlying-table-and-column names have some possible usefulness for
> issuing updates against the underlying tables,

Agreed.

  but what the heck are you
> going to do with "d" here?

you can (as you know) have multiple table aliases on the same table, 
where for example 'd' might represent a relation value to c. For example 
a manager 'd' over a employee 'c':

select c.firstName, d.firstName from contacts as c, contacts as d where 
c.bossId=d.pk and d.role=...

With the table name alias I can tell which rows are bosses or 
subordinates (without needing to know/maintain column ordinality, or 
otherwise just by doing a `select * from..`). The real world use I have 
is similar to above except that I add in another table for one to many 
relations (multiple bosses or trainers, etc), and don't really use the 
bossId field -- it exemplifies the idea here though.

For low level and efficient data retrieval this this is the very 
simplest and most elegant SELECT methodology I've come across - just 
select * from [joined tables], and a diver can build hierarchal 
hashtables, or instead a flat one with (albeit stringbuffer-built) keys 
(tbl.get("boss.firstName"))


   If the application understands the query
> (which it itself issued, don't forget) well enough to understand what
> "d" conveys, why wouldn't "b" be at least as useful?

Yes, but one isn't in the spec as you stated. 'd' seems the more useful 
to me. And if it defaults to the real name in absence of an alias, that 
seems logical and all-serving also.



In response to

pgsql-jdbc by date

Next:From: Morten AndersenDate: 2007-01-05 08:09:09
Subject: Re: Unexpected NullPointerException in "processDeadParsedQueries()"
Previous:From: Tom LaneDate: 2007-01-05 05:28:52
Subject: Re: Support for DatabaseMetadata: getCatalogName, getTableName,

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group