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

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 (view raw or flat)
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

pgsql-jdbc by date

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

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