On Sun, 25 Apr 2004, Andrew Dunstan wrote:
> >> Why do you want two names? Just keep the original casing, and a boolean
> >> saying if it's quoted or not.
>
> Sorry - brain malfunction - yes, original casing plus boolean would
> work. In effect you could derive the canonical form from those two.
Say that you have this in the table with the identifier
name quoted ---- ------ Foo False
Now you want to add the name "FOO"
FOO True
should you be allowed or is it a clash with the above?
What if you also add "foo"
foo True
One of these two should be forbidden. And what about a quoted "FOO":
FOO False FOO True
This case says it is not enough with an expressional unique index on
(upper(name), quoted). It would be easier to enforce uniqueness if one
store both the converted name and the original name:
name orig_name ---- --------- FOO NULL <-- quoted one FOO FOO
<--unquoted one
and the first case
FOO Foo <-- unquoted FOO NULL <-- clashes with the first, good foo
NULL <-- no clash, works fine
With this one can always use upper case translation as per sql spec and
psql can optionally show all unquoted identifiers as upper, lower or mixed
case.
Then we also have the INFORMATION_SCHEMA that should show the names in
UPPER CASE when not quoted, this since applications that are written for
the standard might depend on that (probably no application do today but it
would be a valid case of use of the information schema).
--
/Dennis Björklund