Re: Does anybody use ORDER BY x USING y?

On Sun, Sep 18, 2005 at 11:23:01PM -0400, Tom Lane wrote:
<snip>
> class families" to relate opclasses for different datatypes. Basically
> I'd like to solve most of these issues by constructing a new layer atop
> opclasses, not by deciding that an opclass doesn't convey the full story
> about the behavior of an index column.

Where I'm currently going is creating a table of COLLATE orders. These
collate orders would refer to operator classes but "tweak" them. For
example, things like:

- Sort ascending or descending (descending reverses the bt*cmp test)
- NULLs first or last
- Locale for text types
- etc

They could be declared in the operator class definition, or generated
automatically. You could then do things like:

CREATE INDEX ... (field1 COLLATE ascending, field2 COLLATE descending)

for those queries where you want ascending on one column and descending
on another. Or perhaps:

CREATE INDEX ... (textfield COLLATE ignore_case)
CREATE INDEX ... (textfield COLLATE locale_us)
CREATE INDEX ... (textfield COLLATE optimise_regex)
CREATE INDEX ... (point COLLATE distance)

However, I can't see how this can relate "families" of operator classes
like you talk about Tom. ISTM that needs to dealt with somewhere else,
given that it's unrelated to order.

This is going way out of spec though...
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.