Weird behavior with custom operators

Greetings,

I am using postgresql 8.4 (debian backport). In order to optimize some
of my code I decided to go with a custom data type to which I associated
operators and an operator class for indexation.

Here is the code I use :

-- 8<-------

CREATE TYPE tagvalue AS (storedvalue text);

CREATE OR REPLACE FUNCTION num_lt(tagvalue, double precision)
RETURNS BOOLEAN AS $$
DECLARE
r FLOAT;
retval BOOLEAN;
BEGIN
r := CAST(($1).storedvalue AS double precision);
SELECT r < $2 INTO retval;
RETURN retval;
EXCEPTION WHEN OTHERS THEN
RETURN FALSE;
END;
$$
LANGUAGE plpgsql IMMUTABLE;

CREATE OR REPLACE FUNCTION num_gt(tagvalue, double precision)
RETURNS BOOLEAN AS $$
DECLARE
r FLOAT;
retval BOOLEAN;
BEGIN
r := CAST(($1).storedvalue AS double precision);
SELECT r > $2 INTO retval;
RETURN retval;
EXCEPTION WHEN OTHERS THEN
RETURN FALSE;
END;
$$
LANGUAGE plpgsql IMMUTABLE;

CREATE OR REPLACE FUNCTION num_lte(tagvalue, double precision)
RETURNS BOOLEAN AS $$
DECLARE
r FLOAT;
retval BOOLEAN;
BEGIN
r := CAST(($1).storedvalue AS double precision);
SELECT r <= $2 INTO retval;
RETURN retval;
EXCEPTION WHEN OTHERS THEN
RETURN FALSE;
END;
$$
LANGUAGE plpgsql IMMUTABLE;

CREATE OR REPLACE FUNCTION num_gte(tagvalue, double precision)
RETURNS BOOLEAN AS $$
DECLARE
r FLOAT;
retval BOOLEAN;
BEGIN
r := CAST(($1).storedvalue AS double precision);
SELECT r >= $2 INTO retval;
RETURN retval;
EXCEPTION WHEN OTHERS THEN
RETURN FALSE;
END;
$$
LANGUAGE plpgsql IMMUTABLE;

CREATE OR REPLACE FUNCTION num_eq(tagvalue, double precision)
RETURNS BOOLEAN AS $$
DECLARE
r FLOAT;
retval BOOLEAN;
BEGIN
r := CAST(($1).storedvalue AS double precision);
SELECT r = $2 INTO retval;
RETURN retval;
EXCEPTION WHEN OTHERS THEN
RETURN FALSE;
END;
$$
LANGUAGE plpgsql IMMUTABLE;

CREATE OR REPLACE FUNCTION num_neq(tagvalue, double precision)
RETURNS BOOLEAN AS $$
DECLARE
r FLOAT;
retval BOOLEAN;
BEGIN
r := CAST(($1).storedvalue AS double precision);
SELECT r != $2 INTO retval;
RETURN retval;
EXCEPTION WHEN OTHERS THEN
RETURN FALSE;
END;
$$
LANGUAGE plpgsql IMMUTABLE;

CREATE OR REPLACE FUNCTION txt_lt(tagvalue, text)
RETURNS BOOLEAN AS $$
SELECT ($1).storedvalue < $2;
$$
LANGUAGE 'sql' IMMUTABLE;

CREATE OR REPLACE FUNCTION txt_gt(tagvalue, text)
RETURNS BOOLEAN AS $$
SELECT ($1).storedvalue > $2;
$$
LANGUAGE 'sql' IMMUTABLE;

CREATE OR REPLACE FUNCTION txt_lte(tagvalue, text)
RETURNS BOOLEAN AS $$
SELECT ($1).storedvalue <= $2;
$$
LANGUAGE 'sql' IMMUTABLE;

CREATE OR REPLACE FUNCTION txt_gte(tagvalue, text)
RETURNS BOOLEAN AS $$
SELECT ($1).storedvalue >= $2;
$$
LANGUAGE 'sql' IMMUTABLE;

CREATE OR REPLACE FUNCTION txt_eq(tagvalue, text)
RETURNS BOOLEAN AS $$
SELECT ($1).storedvalue = $2;
$$
LANGUAGE 'sql' IMMUTABLE;

CREATE OR REPLACE FUNCTION txt_neq(tagvalue, text)
RETURNS BOOLEAN AS $$
SELECT ($1).storedvalue != $2;
$$
LANGUAGE 'sql' IMMUTABLE;

CREATE OR REPLACE FUNCTION tv_lt(tagvalue, tagvalue)
RETURNS BOOLEAN AS $$
SELECT ($1).storedvalue < ($2).storedvalue;
$$
LANGUAGE 'sql' IMMUTABLE;

CREATE OR REPLACE FUNCTION tv_gt(tagvalue, tagvalue)
RETURNS BOOLEAN AS $$
SELECT ($1).storedvalue > ($2).storedvalue;
$$
LANGUAGE 'sql' IMMUTABLE;

CREATE OR REPLACE FUNCTION tv_lte(tagvalue, tagvalue)
RETURNS BOOLEAN AS $$
SELECT ($1).storedvalue <= ($2).storedvalue;
$$
LANGUAGE 'sql' IMMUTABLE;

CREATE OR REPLACE FUNCTION tv_gte(tagvalue, tagvalue)
RETURNS BOOLEAN AS $$
SELECT ($1).storedvalue >= ($2).storedvalue;
$$
LANGUAGE 'sql' IMMUTABLE;

CREATE OR REPLACE FUNCTION tv_eq(tagvalue, tagvalue)
RETURNS BOOLEAN AS $$
SELECT ($1).storedvalue = ($2).storedvalue;
$$
LANGUAGE 'sql' IMMUTABLE;

CREATE OR REPLACE FUNCTION tv_eq(tagvalue, tagvalue)
RETURNS BOOLEAN AS $$
SELECT ($1).storedvalue = ($2).storedvalue;
$$
LANGUAGE 'sql' IMMUTABLE;

CREATE OR REPLACE FUNCTION tv_neq(tagvalue, tagvalue)
RETURNS BOOLEAN AS $$
SELECT ($1).storedvalue != ($2).storedvalue;
$$
LANGUAGE 'sql' IMMUTABLE;

CREATE OPERATOR > (
LEFTARG = tagvalue,
RIGHTARG = double precision,
PROCEDURE = num_gt,
commutator = <,
negator = <=,
RESTRICT = scalargtsel,
JOIN = scalargtjoinsel
);

CREATE OPERATOR < (
LEFTARG = tagvalue,
RIGHTARG = double precision,
PROCEDURE = num_lt,
commutator = >,
negator = >=,
RESTRICT = scalarltsel,
JOIN = scalarltjoinsel
);

CREATE OPERATOR >= (
LEFTARG = tagvalue,
RIGHTARG = double precision,
PROCEDURE = num_gte,
commutator = <=,
negator = <,
RESTRICT = scalargtsel,
JOIN = scalargtjoinsel
);

CREATE OPERATOR <= (
LEFTARG = tagvalue,
RIGHTARG = double precision,
PROCEDURE = num_lte,
commutator = >=,
negator = >,
RESTRICT = scalarltsel,
JOIN = scalarltjoinsel
);

CREATE OPERATOR = (
LEFTARG = tagvalue,
RIGHTARG = double precision,
PROCEDURE = num_eq,
commutator = =,
negator = !=,
RESTRICT = eqsel,
JOIN = eqjoinsel,
HASHES,
MERGES
);

CREATE OPERATOR != (
LEFTARG = tagvalue,
RIGHTARG = double precision,
PROCEDURE = num_neq,
commutator = !=,
negator = =,
RESTRICT = neqsel,
JOIN = neqjoinsel
);

CREATE OPERATOR > (
LEFTARG = tagvalue,
RIGHTARG = text,
PROCEDURE = txt_gt,
commutator = <,
negator = <=,
RESTRICT = scalargtsel,
JOIN = scalargtjoinsel
);

CREATE OPERATOR < (
LEFTARG = tagvalue,
RIGHTARG = text,
PROCEDURE = txt_lt,
commutator = >,
negator = >=,
RESTRICT = scalarltsel,
JOIN = scalarltjoinsel
);

CREATE OPERATOR >= (
LEFTARG = tagvalue,
RIGHTARG = text,
PROCEDURE = txt_gte,
commutator = <=,
negator = <,
RESTRICT = scalargtsel,
JOIN = scalargtjoinsel
);

CREATE OPERATOR <= (
LEFTARG = tagvalue,
RIGHTARG = text,
PROCEDURE = txt_lte,
commutator = >=,
negator = >,
RESTRICT = scalarltsel,
JOIN = scalarltjoinsel
);

CREATE OPERATOR = (
LEFTARG = tagvalue,
RIGHTARG = text,
PROCEDURE = txt_eq,
commutator = =,
negator = !=,
RESTRICT = eqsel,
JOIN = eqjoinsel,
HASHES,
MERGES
);

CREATE OPERATOR != (
LEFTARG = tagvalue,
RIGHTARG = text,
PROCEDURE = txt_neq,
commutator = !=,
negator = =,
RESTRICT = neqsel,
JOIN = neqjoinsel
);

CREATE OPERATOR > (
LEFTARG = tagvalue,
RIGHTARG = tagvalue,
PROCEDURE = tv_gt,
commutator = <,
negator = <=,
RESTRICT = scalargtsel,
JOIN = scalargtjoinsel
);

CREATE OPERATOR < (
LEFTARG = tagvalue,
RIGHTARG = tagvalue,
PROCEDURE = tv_lt,
commutator = >,
negator = >=,
RESTRICT = scalarltsel,
JOIN = scalarltjoinsel
);

CREATE OPERATOR >= (
LEFTARG = tagvalue,
RIGHTARG = tagvalue,
PROCEDURE = tv_gte,
commutator = <=,
negator = <,
RESTRICT = scalargtsel,
JOIN = scalargtjoinsel
);

CREATE OPERATOR <= (
LEFTARG = tagvalue,
RIGHTARG = tagvalue,
PROCEDURE = tv_lte,
commutator = >=,
negator = >,
RESTRICT = scalarltsel,
JOIN = scalarltjoinsel
);

CREATE OPERATOR = (
LEFTARG = tagvalue,
RIGHTARG = tagvalue,
PROCEDURE = tv_eq,
commutator = =,
negator = !=,
RESTRICT = eqsel,
JOIN = eqjoinsel,
HASHES,
MERGES
);

CREATE OPERATOR != (
LEFTARG = tagvalue,
RIGHTARG = tagvalue,
PROCEDURE = tv_neq,
commutator = !=,
negator = =,
RESTRICT = neqsel,
JOIN = neqjoinsel
);

CREATE OR REPLACE FUNCTION tagvalue_cmp(tagvalue, tagvalue)
RETURNS integer AS $$
BEGIN
IF ($1).storedvalue < ($2).storedvalue THEN
RETURN -1;
ELSEIF ($1).storedvalue > ($2).storedvalue THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION tagvalue_hash(tagvalue)
RETURNS integer AS $$
SELECT hashtext(($1).storedvalue);
$$ LANGUAGE 'sql' STRICT IMMUTABLE;

CREATE OPERATOR CLASS tagvalue_btree_ops
DEFAULT FOR TYPE tagvalue USING btree AS
OPERATOR 1 < ,
OPERATOR 2 <= ,
OPERATOR 3 = ,
OPERATOR 4 >= ,
OPERATOR 5 > ,
FUNCTION 1 tagvalue_cmp(tagvalue, tagvalue);

CREATE OPERATOR CLASS tagvalue_hash
DEFAULT FOR TYPE tagvalue USING hash AS
OPERATOR 1 =,
FUNCTION 1 tagvalue_hash(tagvalue);

-- 8<-------

I use this on the following table:

CREATE TABLE tags (
id bigint NOT NULL,
name text NOT NULL,
value tagvalue NOT NULL
);

All the operators work as expected, excepted for > and >= :

xxx=> SELECT value FROM tags WHERE value < 3 LIMIT 1;
value
-------
(2)
(1 row)

xxx=> SELECT value FROM tags WHERE value = 3 LIMIT 1;
value
-------
(3)
(1 row)

xxx=> SELECT value FROM tags WHERE value > 3 LIMIT 1;
ERROR: unsupported type: 17886

Obviously there's got to be something wrong with these operators, but
when I use the comparison function directly, it works fine :

xxx=> SELECT value FROM tags WHERE num_gt(value, 3) LIMIT 1;
value
-------
(21)
(1 row)

I am at a loss of ideas about what causes this behavior. Any help
welcome !

Matthieu Huin