Re: [HACKERS] PQfnumber and quoted identifiers

[ pgsql-interfaces added to cc list ]

Andreas Pflug <pgadmin(at)pse-consulting(dot)de> writes:
> Peter Eisentraut wrote:
>> There was a discussion on -interfaces that might need more consideration.
>>
>> http://archives.postgresql.org/pgsql-interfaces/2003-09/msg00026.php
>>
>> Apparently, it has so far been an undocumented feature of libpq's function
>> PGfnumber (return column number from column name) that the column name
>> needs to be double-quoted if it contains upper-case letters. That, is you
>> need to write
>>
>> PQfnumber(res, "\"Bar\"")
>>
>> I think this is completely bizarre and pointless. This is a C interface
>> and not SQL. Other libpq functions that accept names of SQL objects don't
>> do this. Also, PQfname and PQfnumber ought to be inverses.
>>
>> Since this behavior was undocumented and no one had noticed it in the last
>> 10 years, I think we can away with removing it.

> I don't agree; you'll certainly break all libpq apps that contact
> databases with columns that have uppercase or special chars, and the
> failure might be very subtle because in most cases you wouldn't expect
> that this function call fails after you successfully created a rowset.

That was pretty much the argument that carried the day in the earlier
thread. However, I'm not sure how many people really use PQfnumber
(as opposed to hard-wiring assumptions about returned column numbers),
and it would seem that the intersection of those people with people who
use mixed-case column names may be nearly the empty set. If a lot of
people did this, the behavior would have been discussed and documented
(or changed) long ago. So I'm not convinced that we'd really break
very many apps by changing to the behavior that everyone seems to agree
is more sensible.

A data point is that we did make comparable changes to the handling of
database names a couple releases ago, and we got few if any gripes.

Another data point is that the original Berkeley coding of PQfnumber
did not have the case folding/dequoting behavior. The history seems
to be:

Original code: straight strcmp() of argument against returned column name

1997-05-19 23:38: replace strcmp() with strcasecmp() (no dequoting
logic, pretty obviously a broken idea in hindsight)

1997-11-10 00:10: attempted to implement the current behavior of
dequoting+downcasing, but due to a typo, the actual effect was to revert
the behavior to exact match

1999-02-03 15:19: fix typo, installing the current behavior

So other than the shortlived 6.2 release, releases before 6.5 had the
behavior Peter wants. I find it interesting that it took more than a
year for anyone to notice that the putative dequoting+downcasing logic
installed for 6.3 didn't work.

regards, tom lane