Re: \crosstabview fixes - Mailing list pgsql-hackers

    Christoph Berg wrote:

> If there's no way out, what about changing it the other way, i.e.
> breaking the case where the column is named by a number? That seems
> much less of a problem in practice.

I don't think it would be acceptable.
But there's still the option of keeping the dedicated parser.

crosstabview doc says:
"The usual SQL case folding and quoting rules apply to column names"

Tom objected to that, upthread:
> I noticed that the \crosstabview documentation asserts that column
> name arguments are handled per standard SQL semantics.  In point of
> fact, though, the patch expends a couple hundred lines to implement
> what is NOT standard SQL semantics: matching unquoted names
> case-insensitively is anything but that.

Indeed it differs, but that's not necessarily bad. If there's a FOO
column and it's refered to as \crosstabview foo... it will complain about
an ambiguity only if there's another column that's named foo, or Foo, or
any case variant. Quoting becomes mandatory only in that case,
or of course if the column referred to contains spaces or double
quotes.

For example, both these invocations work:
current=# SELECT 'X' as "FOO", 'Y', 'z' \crosstabview foo 2FOO | Y
-----+---X   | z

current=# SELECT 'X' as "FOO", 'Y', 'z' \crosstabview FOO 2FOO | Y
-----+---X   | z

OTOH a detected ambiguity would be:
current=# SELECT 'X' as "FOO", 'Y' as "foo", 'z' \crosstabview FOO 2
Ambiguous column name: FOO

which is solved by quoting the argument:
current=# SELECT 'X' as "FOO", 'Y' as "foo", 'z' \crosstabview "FOO" 2FOO | Y
-----+---X   | z


Whereas using the generic column parser with Tom's patch, the only
accepted invocation out of the 4 examples above is the last one:

new=# SELECT 'X' as "FOO", 'Y', 'z' \crosstabview foo 2
\crosstabview: column name not found: "foo"

new # SELECT 'X' as "FOO", 'Y', 'z' \crosstabview FOO 2
\crosstabview: column name not found: "foo"

new=# SELECT 'X' as "FOO", 'Y' as "foo", 'z' \crosstabview FOO 2
\crosstabview: vertical and horizontal headers must be different columns

new=# SELECT 'X' as "FOO", 'Y' as "foo", 'z' \crosstabview "FOO" 2FOO | Y
-----+---X   | z

Personally I prefer the current behavior, but I can also understand
the appeal from a maintainer's point of view of getting rid of it in
favor of already existing, well-tested generic code.
In which case, what the dedicated parser does is a moot point.

However, because of the aforementioned problem of the interpretation
of column names as numbers, maybe the balance comes back to the
dedicated parser? In which case, there's the question of whether how
it handles case folding as shown above is OK, or whether it should
just downcase unquoted identifiers to strictly match SQL rules.


Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite