Operator Class for Hash

["Jozsef Szalay" <jszalay(at)storediq(dot)com>]

Hi All,

 

Could someone please help me out with an example on how to define an operator and operator class that supports hash joins? I’ve tried to follow the instructions in the documentation for v8.1 but I’m obviously doing something wrong, because the engine crashes on an operation that tries to use the operator.  

 

Here is what I have:

 

CREATE OPERATOR < (leftarg = mytype, rightarg = mytype, procedure = mytype_lt, commutator = >, negator = >=, restrict = scalarltsel, join = scalarltjoinsel, HASHES);

CREATE OPERATOR <= (leftarg = mytype, rightarg = mytype, procedure = mytype_le, commutator = >=, negator = >, restrict = scalarltsel, join = scalarltjoinsel, HASHES, sort1 = <, sort2 = <);

CREATE OPERATOR = (leftarg = mytype, rightarg = mytype, procedure = mytype_eq, commutator = =, negator = <>, restrict = eqsel, join = eqjoinsel, HASHES, sort1 = <, sort2 = <);

CREATE OPERATOR >= (leftarg = mytype, rightarg = mytype, procedure = mytype_ge, commutator = <=, negator = <, restrict = scalargtsel, join = scalargtjoinsel, HASHES, sort1 = <, sort2 = <);

CREATE OPERATOR > (leftarg = mytype, rightarg = mytype, procedure = mytype_gt, commutator = <, negator = <=, restrict = scalargtsel, join = scalargtjoinsel, HASHES, sort1 = <, sort2 = <);

CREATE OPERATOR <> (leftarg = mytype, rightarg = mytype, procedure = mytype_ne, commutator = <>, negator = =, restrict = neqsel, join = neqjoinsel, HASHES, sort1 = <, sort2 = <);

 

CREATE OPERATOR CLASS mytype_ops DEFAULT FOR TYPE mytype USING btree AS

   OPERATOR 1 <,

   OPERATOR 2 <=,

   OPERATOR 3 =,

   OPERATOR 4 >=,

   OPERATOR 5 >,

   FUNCTION 1 mytype_comp(mytype, mytype);

 

CREATE OPERATOR CLASS mytype_ops DEFAULT FOR TYPE mytype USING hash AS

   OPERATOR 1 =,

   FUNCTION 1 mytype_comp(mytype, mytype);

 

 

Thank you for the help!