Dear Tom,
Still for the sake of argument:
> Note that (2) fails for long names; you have to do something to
> compress to NAMEDATALEN.
Indeed.
What if the type is changed to TEXT? It is just a view after all.
How important is it to stick to "sql_identifier"?
> The big problem with either of these is that it's no longer easily
> possible to extract the actual constraint name from the view.
Sure. A function is provided to do so, say
information_schema_constraint_name_to_pg_constraint_name.
> In any case, I am fairly sure that not having the constraint_name column
> show the actual constraint name is a violation of the spirit of the SQL
> spec, whether or not you can claim that it meets the letter.
Well, one must choose between to evil:
(1) the constraint_name is changed in the view to be unique as expected
by the spec, and the data can be joined meaningfully, and some reliable
information can be derived.
(2) the constraint_name looks nice but is not unique, and
the information in the view is ambiguous and cannot be relied upon,
so one is back to square "postgresql supports the information_schema,
but there is no point to query it and expecting the results to
reflect the contents of the catalogs".
If you want to stick to both the letter and the spirit of the spec, that
would mean enforcing unique constraint names in pg and break every
applications. Not good.
ISTM that the "spirit" of the information schema is more to be useful (1)
than to look beautiful (2).
Another technical proposal, a little more subtle and with possible
underlying issues I cannot foresee: have the constraint_name be a "pair of
sql_identifiers".
--
Fabien.