Weird behavior with custom operators

[Matthieu HUIN <matthieu.huin@xxxxxxxxxx>]

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


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general