Re: Metadata for tables, schema, and numeric

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Ola Sundell <ola(at)miranda(dot)org>
Cc: Peter Eisentraut <peter_e(at)gmx(dot)net>, Mike Finn <mike(dot)finn(at)tacticalExecutive(dot)com>, pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Metadata for tables, schema, and numeric
Date: 2001-07-05 00:54:28
Message-ID: 1545.994294468@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

Ola Sundell <ola(at)miranda(dot)org> writes:
> On Thu, 5 Jul 2001, Peter Eisentraut wrote:
>> These functions are fundamentally flawed, because it's impossible to
>> determine the table name that a result column came from, except in the
>> most simple cases in which case you don't need these functions.

> I disagree. You can either parse the query at the client side, which is
> flawed, imo, or you can make the backend send the information along with
> the rest of the Field metadata.

Peter's point is that in the general case there is no reasonable answer.
A few counterexamples:

select a.f1 + b.f2 as sum from a,b where ...

Which table would you like to say the output column "sum" is from?

select * from a inner join b using (id);

This query will join a and b on "a.id = b.id". Per SQL92, the output
will have only one column named id, not two. Which table would you like
to say the column came from?

select f1 from a UNION select f2 from b;

The usual question.

> One thing you might want to consider,
> though, is that the information is necessary for the
> UpdateableResultSets. How, else, are you going to execute the updates?

While I haven't read the JDBC spec, I'd have to guess that the entire
notion of an updateable result set is defined only for a very small
subset of possible SELECT queries --- small enough that deducing the
requested information is pretty trivial. In particular, I don't
believe that the notion of an updateable result set can be considered
well-defined for *any* query that involves a join. Therefore there is
only one table in the FROM clause, and deducing the correct result
for getTableName() is trivial.

regards, tom lane

In response to

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message George Koras 2001-07-05 08:32:00 Re: [INTERFACES] New code for JDBC driver
Previous Message Mike Finn 2001-07-04 22:48:04 Re: Metadata for tables, schema, and numeric