BUG #2204: Feature Req: Unique output column names

Brandon Black wrote:

I realize this is fixable by the user with "AS count_x", this is more an
issue that came in writing cross-vendor ORM modules in perl, where most
other database vendors offer some sort of unique naming convention, but
postgres' output column names are indistinguishable, for the same query.

Use the AS clause, which is the one that actually complies with the SQL
standard. Or show us where the standard mandates name munging and we
will implement it.

--
Alvaro Herrera http://www.advogato.org/person/alvherre
"La fuerza no est� en los medios f�sicos
sino que reside en una voluntad indomable" (Gandhi)

Alvaro Herrera <alvherre@alvh.no-ip.org> writes:

Or show us where the standard mandates name munging and we
will implement it.

SQL92 says (in 7.9 <query specification>):

9) Case:

a) If the i-th <derived column> in the <select list> specifies
an <as clause> that contains a <column name> C, then the
<column name> of the i-th column of the result is C.

b) If the i-th <derived column> in the <select list> does not
specify an <as clause> and the <value expression> of that
<derived column> is a single <column reference>, then the
<column name> of the i-th column of the result is C.

c) Otherwise, the <column name> of the i-th column of the <query
specification> is implementation-dependent and different
from the <column name> of any column, other than itself, of
a table referenced by any <table reference> contained in the
SQL-statement.

Rule (c) is a bit interesting since it does NOT say that a generated
name has to be different from the generated names of other columns of
the same SELECT; rather it has to be different from names of columns
in base tables used by the SELECT. Not sure why they did that. We
don't really honor this rule at present.

In any case, rules (a) and (b) *directly* contradict the notion that
output column names can be guaranteed unique.

Also notice that generated names are "implementation-dependent" not
"implementation-defined" --- that means that we do not have to document
what the generation process is, nor promise that it won't change.
This means that any application that depends on generated names to be
particular things is violating the spec, and has only itself to blame
when it breaks. You should be using AS if you want stable names to
refer to these columns with.

I haven't looked at SQL99 or SQL2003, but most likely they say the same
thing in two or three times as many words ;-)

regards, tom lane

Tom Lane wrote:

I haven't looked at SQL99 or SQL2003, but most likely they say the same
thing in two or three times as many words ;-)

Thanks for the pointer. In SQL 2003, this is 7.12
<query specification>:

17) Case:
a) If the i-th <derived column> in the <select list> specifies an
<as clause> that contains a <column name> CN, then the <column name>
of the i-th column of the result is CN.
b) If the i-th <derived column> in the <select list> does not specify an
<as clause> and the <value expression> of that <derived column> is a
single column reference, then the <column name> of the i-th column of
the result is the <column name> of the column designated by the
column reference.
c) Otherwise, the <column name> of the i-th column of the <query
specification> is implementation dependent.

So they actually simplified the third rule and we comply with it!

It would be nice to have this text in HTML or info format. This PDF
mess is really awkward.

--
Alvaro Herrera http://www.PlanetPostgreSQL.org
"We are who we choose to be", sang the goldfinch
when the sun is high (Sandman)

Alvaro Herrera <alvherre@alvh.no-ip.org> writes:

It would be nice to have this text in HTML or info format. This PDF
mess is really awkward.

Yeah, that's why I tend to look at SQL92 or SQL99 first ... I have those
drafts in plain-ASCII format, which is so much easier to grep and copy.
Would love to lay my hands on plain-ASCII SQL2003.

regards, tom lane

Tom Lane wrote:

Alvaro Herrera <alvherre@alvh.no-ip.org> writes:

It would be nice to have this text in HTML or info format. This PDF
mess is really awkward.

Yeah, that's why I tend to look at SQL92 or SQL99 first ... I have those
drafts in plain-ASCII format, which is so much easier to grep and copy.
Would love to lay my hands on plain-ASCII SQL2003.

What I use is the text version I extracting using pdf2text (or something
like that). While the formatting is very awkward, it is at least
greppable.

--
Alvaro Herrera http://www.amazon.com/gp/registry/CTMLCN8V17R4
The easiest way to resolve [trivial code guidelines disputes] is to fire
one or both of the people involved. (Damian Conway)

On 1/24/06, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:

Tom Lane wrote:

I haven't looked at SQL99 or SQL2003, but most likely they say the same
thing in two or three times as many words ;-)

Thanks for the pointer. In SQL 2003, this is 7.12
<query specification>:

17) Case:
a) If the i-th <derived column> in the <select list> specifies an
<as clause> that contains a <column name> CN, then the <column name>
of the i-th column of the result is CN.
b) If the i-th <derived column> in the <select list> does not specify an
<as clause> and the <value expression> of that <derived column> is a
single column reference, then the <column name> of the i-th column of
the result is the <column name> of the column designated by the
column reference.
c) Otherwise, the <column name> of the i-th column of the <query
specification> is implementation dependent.

So they actually simplified the third rule and we comply with it!

It would be nice to have this text in HTML or info format. This PDF
mess is really awkward.

Well, then the current behavior is kosher in SQL2003 then, but not
neccesarily for SQL92 if someone decided to define some literal column
names like "count" or "sum" :)

It's not a big deal, just seemed that in certain situations the way
other vendors have done it ( "count(x)", "count(y)" ... or .. "1",
"2") at least avoid duplication in common cases.

-- Brandon

Alvaro Herrera wrote:

Tom Lane wrote:

Alvaro Herrera <alvherre@alvh.no-ip.org> writes:

It would be nice to have this text in HTML or info format. This PDF
mess is really awkward.

Yeah, that's why I tend to look at SQL92 or SQL99 first ... I have those
drafts in plain-ASCII format, which is so much easier to grep and copy.
Would love to lay my hands on plain-ASCII SQL2003.

What I use is the text version I extracting using pdf2text (or something
like that). While the formatting is very awkward, it is at least
greppable.

I can export text from Acrobat on XP. Have you tried that?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073