Re: [HACKERS] Case Preservation disregarding case

On 12/5/06, Ken Johanson <pg-user(at)kensystem(dot)com> wrote:
>
> Bruce Momjian wrote:
> > Tom Lane wrote:
> >>
> >> The real bottom line, though, is that this community has little respect
> >> for proposals that involve moving away from the SQL spec rather than
> >> closer to it; and that's what you're asking us to do. The spec is not
> >> at all vague about the case-sensitivity of identifiers. Now certainly
> >> we are not doing exactly what the spec says, but what you ask is even
> >> less like the spec's requirements.
> >
> > I think there are two major issues here:
> >
> > o if you quote identifiers that have any upper-case characters,
> > do it both at table creation and use
> > o display of non-quoted identifiers is lower-case
> >
> > I think we are OK making people either always quote, or always not
> > quote. What we don't currently have a good answer for is people wanting
> > the identifiers displayed using the original case. You can use quotes
> > all the time of you want such display, but it is a pain. I think this
> > is the crux of the complaints.
> >
> > Saving the case of the original creation and displaying that does work,
> > but then it isn't clear if the identifier needs quotes (is the
> > upper-case real or just for display). This gets us into even more
> > confusion.
> >
> > Can someone can think of an answer to all this?
> >
>
> I believe there is no one answer that will solve all cases.. but one
> solution that might come close is something like this (I take no credit,
> others have described this before me):
>
> PG would support two modes of operation:
>
> 1) Standard compliant, and;
>
> 2) Quirks / compatibility mode (case preserving but case-insensitive)
>
> I believe (and may be wrong) that the #2 mode-of-operation would only
> require:
>
> a) resultset data to have it's (unquoted) identifiers to be case-folded,
> and;
> b) queries with (unquoted) identifiers (joins / aliases etc) would
> continue in the planner to be case folded, so would work as now (and in
> effect be case-insensitive).
> c) the table describe syntax would return the case-preserved id name
> (which it already does if quoted?), or d:
> d) in addition to a), optionally send metadata describing the
> case-preserved name; this might someday allow newer drivers to display
> (display only, not column lookup) those names in database-designer views
> (iSQL types apps)
>
> If #a is possible, then drivers should not break, even if the DB is
> config'd to use setting #2. But I don't know the low-level protocol of
> PG to know if that is possible. ..
>
> Hopefully I'm not missing any points here, please correct me if so...
>
> I believe what I described above (while not being standard complaint per
> se) is identical to how mysql and mssql work (operationally anyway)
> right now.. On the other had Oracle and others work as PG does now, but
> my point in discussing this, is that the first two DBs have enough
> market, that offering a compatibility mode to ease the burden of porting
> apps would have substantial value (I know this from experience)
>

Wasn't the whole reason this came up because someone wanted to do SELECT *
FROM some_table and have the returned columns returned in a case that was
viewable? This thread has gone on for so long I don't remember for sure and
I have not kept up with the thread very well.

If this is the "case", no pun intended, then why not create views like this:

CREATE VIEW my_view (
"Person's ID",
"Person's Name"
) AS
SELECT
person_id,
first_name || ' ' || last_name
FROM person
;

When I do a SELECT * FROM my_view, I get "Person's ID", not "PERSON'S ID" or
"person's id".

--
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==================================================================