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.
--- WannaBuild Database Schema for PostgreSQL -*- sql -*- --- Debian version type and operators --- --- Code derived from Dpkg::Version: --- Copyright © Colin Watson <cjwatson@xxxxxxxxxx> --- Copyright © Ian Jackson <iwj@xxxxxxxxxx> --- Copyright © 2007 by Don Armstrong <don@xxxxxxxxxxxxxxxx> --- --- PostgreSQL SQL, PL/pgSQL and PL/Perl: --- Copyright © 2008 Roger Leigh <rleigh@xxxxxxxxxx> --- --- This program is free software: you can redistribute it and/or modify --- it under the terms of the GNU General Public License as published by --- the Free Software Foundation, either version 2 of the License, or --- (at your option) any later version. --- --- This program is distributed in the hope that it will be useful, but --- WITHOUT ANY WARRANTY; without even the implied warranty of --- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU --- General Public License for more details. --- --- You should have received a copy of the GNU General Public License --- along with this program. If not, see --- <http://www.gnu.org/licenses/>. SET SESSION plperl.use_strict TO 't'; CREATE DOMAIN debversion AS TEXT; COMMENT ON DOMAIN debversion IS 'Debian package version number'; ALTER DOMAIN debversion ADD CONSTRAINT debversion_syntax CHECK (VALUE !~ '[^-+:.0-9a-zA-Z~]'); -- From Dpkg::Version::parseversion CREATE OR REPLACE FUNCTION debversion_split (debversion) RETURNS text[] AS $$ my $ver = shift; my %verhash; if ($ver =~ /:/) { $ver =~ /^(\d+):(.+)/ or die "bad version number '$ver'"; $verhash{epoch} = $1; $ver = $2; } else { $verhash{epoch} = 0; } if ($ver =~ /(.+)-(.*)$/) { $verhash{version} = $1; $verhash{revision} = $2; } else { $verhash{version} = $ver; $verhash{revision} = 0; } return [$verhash{'epoch'}, $verhash{'version'}, $verhash{'revision'}]; $$ LANGUAGE plperl IMMUTABLE STRICT; COMMENT ON FUNCTION debversion_split (debversion) IS 'Split debian version into epoch, upstream version and revision'; CREATE OR REPLACE FUNCTION debversion_epoch (version debversion) RETURNS text AS $$ DECLARE split text[]; BEGIN split := debversion_split(version); RETURN split[1]; END; $$ LANGUAGE plpgsql IMMUTABLE STRICT; COMMENT ON FUNCTION debversion_epoch (debversion) IS 'Get debian version epoch'; CREATE OR REPLACE FUNCTION debversion_version (version debversion) RETURNS text AS $$ DECLARE split text[]; BEGIN split := debversion_split(version); RETURN split[2]; END; $$ LANGUAGE plpgsql IMMUTABLE STRICT; COMMENT ON FUNCTION debversion_version (debversion) IS 'Get debian version upstream version'; CREATE OR REPLACE FUNCTION debversion_revision (version debversion) RETURNS text AS $$ DECLARE split text[]; BEGIN split := debversion_split(version); RETURN split[3]; END; $$ LANGUAGE plpgsql IMMUTABLE STRICT; COMMENT ON FUNCTION debversion_revision (debversion) IS 'Get debian version revision'; -- From Dpkg::Version::parseversion CREATE OR REPLACE FUNCTION debversion_compare_single (version1 text, version2 text) RETURNS integer AS $$ sub order{ my ($x) = @_; ##define order(x) ((x) == '~' ? -1 \ # : cisdigit((x)) ? 0 \ # : !(x) ? 0 \ # : cisalpha((x)) ? (x) \ # : (x) + 256) # This comparison is out of dpkg's order to avoid # comparing things to undef and triggering warnings. if (not defined $x or not length $x) { return 0; } elsif ($x eq '~') { return -1; } elsif ($x =~ /^\d$/) { return 0; } elsif ($x =~ /^[A-Z]$/i) { return ord($x); } else { return ord($x) + 256; } } sub next_elem(\@){ my $a = shift; return @{$a} ? shift @{$a} : undef; } my ($val, $ref) = @_; $val = "" if not defined $val; $ref = "" if not defined $ref; my @val = split //,$val; my @ref = split //,$ref; my $vc = next_elem @val; my $rc = next_elem @ref; while (defined $vc or defined $rc) { my $first_diff = 0; while ((defined $vc and $vc !~ /^\d$/) or (defined $rc and $rc !~ /^\d$/)) { my $vo = order($vc); my $ro = order($rc); # Unlike dpkg's verrevcmp, we only return 1 or -1 here. return (($vo - $ro > 0) ? 1 : -1) if $vo != $ro; $vc = next_elem @val; $rc = next_elem @ref; } while (defined $vc and $vc eq '0') { $vc = next_elem @val; } while (defined $rc and $rc eq '0') { $rc = next_elem @ref; } while (defined $vc and $vc =~ /^\d$/ and defined $rc and $rc =~ /^\d$/) { $first_diff = ord($vc) - ord($rc) if !$first_diff; $vc = next_elem @val; $rc = next_elem @ref; } return 1 if defined $vc and $vc =~ /^\d$/; return -1 if defined $rc and $rc =~ /^\d$/; return (($first_diff > 0) ? 1 : -1) if $first_diff; } return 0; $$ LANGUAGE plperl IMMUTABLE STRICT; COMMENT ON FUNCTION debversion_compare_single (text, text) IS 'Compare upstream or revision parts of Debian versions'; -- Logic only derived from Dpkg::Version::parseversion CREATE OR REPLACE FUNCTION debversion_compare (version1 debversion, version2 debversion) RETURNS integer AS $$ DECLARE split1 text[]; split2 text[]; result integer; BEGIN result := 0; split1 := debversion_split(version1); split2 := debversion_split(version2); -- RAISE NOTICE 'Version 1: %', version1; -- RAISE NOTICE 'Version 2: %', version2; -- RAISE NOTICE 'Split 1: %', split1; -- RAISE NOTICE 'Split 2: %', split2; IF split1[1] > split2[1] THEN result := 1; ELSIF split1[1] < split2[1] THEN result := -1; ELSE result := debversion_compare_single(split1[2], split2[2]); IF result = 0 THEN result := debversion_compare_single(split1[3], split2[3]); END IF; END IF; RETURN result; END; $$ LANGUAGE plpgsql IMMUTABLE STRICT; COMMENT ON FUNCTION debversion_compare (debversion, debversion) IS 'Compare Debian versions'; CREATE OR REPLACE FUNCTION debversion_eq (version1 debversion, version2 debversion) RETURNS boolean AS $$ DECLARE comp integer; result boolean; BEGIN comp := debversion_compare(version1, version2); result := comp = 0; RETURN result; END; $$ LANGUAGE plpgsql IMMUTABLE STRICT; COMMENT ON FUNCTION debversion_eq (debversion, debversion) IS 'debversion equal'; CREATE OR REPLACE FUNCTION debversion_ne (version1 debversion, version2 debversion) RETURNS boolean AS $$ DECLARE comp integer; result boolean; BEGIN comp := debversion_compare(version1, version2); result := comp <> 0; RETURN result; END; $$ LANGUAGE plpgsql IMMUTABLE STRICT; COMMENT ON FUNCTION debversion_ne (debversion, debversion) IS 'debversion not equal'; CREATE OR REPLACE FUNCTION debversion_lt (version1 debversion, version2 debversion) RETURNS boolean AS $$ DECLARE comp integer; result boolean; BEGIN comp := debversion_compare(version1, version2); result := comp < 0; RETURN result; END; $$ LANGUAGE plpgsql IMMUTABLE STRICT; COMMENT ON FUNCTION debversion_lt (debversion, debversion) IS 'debversion less-than'; CREATE OR REPLACE FUNCTION debversion_gt (version1 debversion, version2 debversion) RETURNS boolean AS $$ DECLARE comp integer; result boolean; BEGIN comp := debversion_compare(version1, version2); result := comp > 0; RETURN result; END; $$ LANGUAGE plpgsql IMMUTABLE STRICT; COMMENT ON FUNCTION debversion_gt (debversion, debversion) IS 'debversion greater-than'; CREATE OR REPLACE FUNCTION debversion_le (version1 debversion, version2 debversion) RETURNS boolean AS $$ DECLARE comp integer; result boolean; BEGIN comp := debversion_compare(version1, version2); result := comp <= 0; RETURN result; END; $$ LANGUAGE plpgsql IMMUTABLE STRICT; COMMENT ON FUNCTION debversion_le (debversion, debversion) IS 'debversion less-than-or-equal'; CREATE OR REPLACE FUNCTION debversion_ge (version1 debversion, version2 debversion) RETURNS boolean AS $$ DECLARE comp integer; result boolean; BEGIN comp := debversion_compare(version1, version2); result := comp >= 0; RETURN result; END; $$ LANGUAGE plpgsql IMMUTABLE STRICT; COMMENT ON FUNCTION debversion_ge (debversion, debversion) IS 'debversion greater-than-or-equal'; CREATE OPERATOR = ( PROCEDURE = debversion_eq, LEFTARG = debversion, RIGHTARG = debversion, COMMUTATOR = =, NEGATOR = != ); COMMENT ON OPERATOR = (debversion, debversion) IS 'debversion equal'; CREATE OPERATOR != ( PROCEDURE = debversion_eq, LEFTARG = debversion, RIGHTARG = debversion, COMMUTATOR = !=, NEGATOR = = ); COMMENT ON OPERATOR != (debversion, debversion) IS 'debversion not equal'; CREATE OPERATOR < ( PROCEDURE = debversion_lt, LEFTARG = debversion, RIGHTARG = debversion, COMMUTATOR = >, NEGATOR = >= ); COMMENT ON OPERATOR < (debversion, debversion) IS 'debversion less-than'; CREATE OPERATOR > ( PROCEDURE = debversion_gt, LEFTARG = debversion, RIGHTARG = debversion, COMMUTATOR = <, NEGATOR = >= ); COMMENT ON OPERATOR > (debversion, debversion) IS 'debversion greater-than'; CREATE OPERATOR <= ( PROCEDURE = debversion_le, LEFTARG = debversion, RIGHTARG = debversion, COMMUTATOR = >=, NEGATOR = > ); COMMENT ON OPERATOR <= (debversion, debversion) IS 'debversion less-than-or-equal'; CREATE OPERATOR >= ( PROCEDURE = debversion_ge, LEFTARG = debversion, RIGHTARG = debversion, COMMUTATOR = <=, NEGATOR = < ); COMMENT ON OPERATOR >= (debversion, debversion) IS 'debversion greater-than-or-equal'; CREATE OPERATOR CLASS debversion_ops DEFAULT FOR TYPE debversion USING BTREE AS OPERATOR 1 <, OPERATOR 2 <=, OPERATOR 3 =, OPERATOR 4 >=, OPERATOR 5 >, FUNCTION 1 debversion_compare(debversion,debversion); CREATE TABLE testv (version debversion); INSERT INTO testv (version) VALUES ('3.0.7+1-2~lenny2'); INSERT INTO testv (version) VALUES ('3.0.7+1-2'); INSERT INTO testv (version) VALUES ('3.0.7+1-1'); INSERT INTO testv (version) VALUES ('1.0.3-3'); SELECT * FROM testv; SELECT * FROM testv ORDER BY version ASC; SELECT * FROM testv ORDER BY version DESC; SELECT '3.0.7+1-2'::debversion < '3.0.7+1-2~lenny2'::debversion; SELECT '3.0.7+1-2'::debversion > '3.0.7+1-2~lenny2'::debversion; SELECT '3.0.7+1-2'::debversion = '3.0.7+1-2~lenny2'::debversion; CREATE TABLE testt (version text); INSERT INTO testt SELECT * FROM testv; SELECT * FROM testt ORDER BY version ASC; SELECT * FROM testt ORDER BY version DESC; SELECT * FROM testt ORDER BY version USING <; SELECT * FROM testv ORDER BY version USING <;
Attachment:
signature.asc
Description: Digital signature
