Operator Class for Hash

Started by Jozsef Szalayalmost 20 years ago2 messagesgeneral
Jump to latest
#1Jozsef Szalay
jszalay@storediq.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!

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Jozsef Szalay (#1)
Re: Operator Class for Hash

"Jozsef Szalay" <jszalay@storediq.com> writes:

Could someone please help me out with an example on how to define an
operator and operator class that supports hash joins?

Well, for starters, only the equality operator should be marked HASHES,
and the support function for a hash opclass is completely different from
the one for a btree opclass.

regards, tom lane