Dear all,
I've created a new domain (debversion) derived from TEXT, which
includes its own operators (< <= = >= > and <>), and also its
own operator class for BTREE indices.
The operators function correctly when I test them by themselves,
e.g. SELECT x < y;
However, if I create a table with a column of this type, ORDER BY
does not result in correct ordering. I have to explicitly add
'USING <' to the query, and even this fails to work if I haven't
defined the operator class:
# SELECT * FROM testv ORDER BY version ASC;
version
------------------
1.0.3-3
3.0.7+1-1
3.0.7+1-2
3.0.7+1-2~lenny2
(4 rows)
# SELECT * FROM testv ORDER BY version USING <;
version
------------------
1.0.3-3
3.0.7+1-1
3.0.7+1-2~lenny2
3.0.7+1-2
(4 rows)
The latter shows the correct ordering. The former appears to be
using the lexical ordering of the TEXT type. Adding an index
does not affect the ordering, even if I explictly make it use my
operator class (it's also set as the default).
The SQL code to create the type and demonstrate the problem follows
at the end of this mail. It requires the PL/Perl and PL/pgSQL
languages to be available. It shows example queries to demonstrate
the ordering issue above.
I thought that I had correctly defined the type, functions, operators
and operator class in order for everything to function correctly, but
I must be missing some final piece of the puzzle or some PostgreSQL
subtlety I'm not aware of (this is my first attempt at defining
operators, and I am also a newcomer to using procedural languages).
Could anyone suggest what I've done wrong here?
Many thanks,
Roger Leigh
--
.''`. Roger Leigh
: :' : Debian GNU/Linux http://people.debian.org/~rleigh/
`. `' Printing on GNU/Linux? http://gutenprint.sourceforge.net/
`- GPG Public Key: 0x25BFB848 Please GPG sign your mail.
