Does anybody use ORDER BY x USING y?

Hi,

PostgreSQL's grammer allows you to specify the operator to sort with in
the ORDER BY clause. Various bits of the backend support this feature,
yet it appears to partially undocumented. I can't find it in the ORDER
BY [1] section but there is a paragraph on it under the SELECT
documentation [2].

I'm asking because SQL COLLATE support is really doing something
similar. I was wondering if instead of adding something in parallel just
replace sortop with collateid. This means all the code relating to
pathkeys won't need to change since we still use OIDs for the pathkeys,
they're just not operator oids anymore.

We can continue to support USING [op] as long as [op] is one of the GT
or LT operators in the OPERATOR CLASS. This restriction may exist
already, I can't tell.

All we lose is the ability to say USING [arbitrary op]. Does anybody
use this. Would people object to requiring the operator after USING to
be part of an operator class?

Have a nice day,

[1] http://www.postgresql.org/docs/8.0/interactive/queries-order.html
[2] http://www.postgresql.org/docs/8.0/interactive/sql-select.html
--
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.

Martjin,

> We can continue to support USING [op] as long as [op] is one of the GT
> or LT operators in the OPERATOR CLASS. This restriction may exist
> already, I can't tell.
>
> All we lose is the ability to say USING [arbitrary op]. Does anybody
> use this. Would people object to requiring the operator after USING to
> be part of an operator class?

Hmmm ... would this prevent the hackish workaround for case-insensitive sort?

--
Josh Berkus
Aglio Database Solutions
San Francisco

On Sun, Sep 18, 2005 at 12:34:10PM -0700, Josh Berkus wrote:
> > All we lose is the ability to say USING [arbitrary op]. Does anybody
> > use this. Would people object to requiring the operator after USING to
> > be part of an operator class?
>
> Hmmm ... would this prevent the hackish workaround for case-insensitive sort?

Err, which hackish workaround would that be? The right solution is
citext which creates it's own operator class. This doesn't have
anything to do with functional indexes either.

I've been using Google to find any interesting use of the USING clause
but havn't found any yet.

Have a nice day,
--
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.

Martijn van Oosterhout wrote:

>On Sun, Sep 18, 2005 at 12:34:10PM -0700, Josh Berkus wrote:
>
>
>>>All we lose is the ability to say USING [arbitrary op]. Does anybody
>>>use this. Would people object to requiring the operator after USING to
>>>be part of an operator class?
>>>
>>>
>>Hmmm ... would this prevent the hackish workaround for case-insensitive sort?
>>
>>
>
>Err, which hackish workaround would that be? The right solution is
>citext which creates it's own operator class. This doesn't have
>anything to do with functional indexes either.
>
>

Last time I looked it appeared to have significant limitations, and some
considerable inefficiencies (e.g, copying the strings and folding them
to canonical case on every comparison). I would certainly be extremely
wary of just saying "that's the solution".

cheers

andrew

On Sun, Sep 18, 2005 at 04:19:06PM -0400, Andrew Dunstan wrote:
> >Err, which hackish workaround would that be? The right solution is
> >citext which creates it's own operator class. This doesn't have
> >anything to do with functional indexes either.
>
> Last time I looked it appeared to have significant limitations, and some
> considerable inefficiencies (e.g, copying the strings and folding them
> to canonical case on every comparison). I would certainly be extremely
> wary of just saying "that's the solution".

Ok, so citext has its limitations. Case-insensetive sort is hard [1].
My real question was, what was the solution he was referring to using
the USING clause?

[1] http://lafstern.org/matt/col2_new.pdf

--
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.

<much discussion on collation and ordering>

I'm going to take from this discussion that there is no use for the
USING clause with operators not in an operator class and that if this
changes we won't be seriously inconveniencing anybody.

Have a nice day,

--
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.