Detailed Configuration of PostgreSQL Integer Operator Family

int4 USING btree FAMILY integer_ops AS -- standard int4 comparisons OPERATOR 1 < , OPERATOR 2 <= , OPERATOR 3 = , OPERATOR 4 >= , OPERATOR 5 > , FUNCTION 1 btint4cmp(int4, int4) , FUNCTION 2 btint4sortsupport(internal) , FUNCTION 3 in_range(int4, int4, int4, boolean, boolean) , FUNCTION 4 btequalimage(oid) , FUNCTION 6 btint4skipsupport(internal) ; CREATE OPERATOR CLASS int2_ops DEFAULT FOR TYPE int2 USING btree FAMILY integer_ops AS -- standard int2 comparisons OPERATOR 1 < , OPERATOR 2 <= , OPERATOR 3 = , OPERATOR 4 >= , OPERATOR 5 > , FUNCTION 1 btint2cmp(int2, int2) , FUNCTION 2 btint2sortsupport(internal) , FUNCTION 3 in_range(int2, int2, int2, boolean, boolean) , FUNCTION 4 btequalimage(oid) , FUNCTION 6 btint2skipsupport(internal) ; ALTER OPERATOR FAMILY integer_ops USING btree ADD -- cross-type comparisons int8 vs int2 OPERATOR 1 < (int8, int2) , OPERATOR 2 <= (int8, int2) , OPERATOR 3 = (int8, int2) , OPERATOR 4 >= (int8, int2) , OPERATOR 5 > (int8, int2) , FUNCTION 1 btint82cmp(int8, int2) , -- cross-type comparisons int8 vs int4 OPERATOR 1 < (int8, int4) , OPERATOR 2 <= (int8, int4) , OPERATOR 3 = (int8, int4) , OPERATOR 4 >= (int8, int4) , OPERATOR 5 > (int8, int4) , FUNCTION 1 btint84cmp(int8, int4) , -- cross-type comparisons int4 vs int2 OPERATOR 1 < (int4, int2) , OPERATOR 2 <= (int4, int2) , OPERATOR 3 = (int4, int2) , OPERATOR 4 >= (int4, int2) , OPERATOR 5 > (int4, int2) , FUNCTION 1 btint42cmp(int4, int2) , -- cross-type comparisons int4 vs int8 OPERATOR 1 < (int4, int8) , OPERATOR 2 <= (int4, int8) , OPERATOR 3 = (int4, int8) , OPERATOR 4 >= (int4, int8) , OPERATOR 5 > (int4, int8) , FUNCTION 1 btint48cmp(int4, int8) , -- cross-type comparisons int2 vs int8 OPERATOR 1 < (int2, int8) , OPERATOR 2 <= (int2, int8) , OPERATOR 3 = (int2, int8) , OPERATOR 4 >= (int2, int8) , OPERATOR 5 > (int2, int8) , FUNCTION 1 btint28cmp(int2, int8) , -- cross-type comparisons int2 vs int4 OPERATOR 1 < (int2, int4) , OPERATOR 2 <= (int2, int4) , OPERATOR 3 = (int2, int4) , OPERATOR 4 >= (int2, int4) , OPERATOR 5 > (int2, int4) , FUNCTION 1 btint24cmp(int2, int4) , -- cross-type in_range functions FUNCTION 3 in_range(int4, int4, int8, boolean, boolean) , FUNCTION 3 in_range(int4, int4, int2, boolean, boolean) , FUNCTION 3 in_range(int2, int2, int8, boolean, boolean) , FUNCTION 3 in_range(int2, int2, int4, boolean, boolean) ; ]]> </programlisting> Notice that this definition <quote>overloads</quote> the operator strategy and support function numbers: each number occurs multiple times within the family. This is allowed so long as each instance of a particular number has distinct input data types. The instances that have both input types equal to an operator class's input type are the primary operators and support functions for that operator class, and in most cases should be declared as part of the operator class rather than as loose members of the family. </para> <para> In a B-tree operator family, all the operators in the family must sort compatibly, as is specified in detail in <xref linkend="btree-behavior"/>. For each operator in the family there must be a support function having the same two input data types as the operator. It is recommended that a family be complete, i.e., for each combination of data types, all operators are included. Each operator class should include just the non-cross-type operators and support function for its data type. </para> <para> To build a multiple-data-type hash operator family, compatible hash support functions must be created for each data type supported by the family. Here compatibility means that the functions are guaranteed to return the same hash code for any two values that are considered equal by the family's equality operators, even when the values are of different

This section demonstrates the configuration of the 'integer_ops' operator family in PostgreSQL. It shows how to create operator classes for int4 and int2 data types, including standard comparisons and support functions. The example then illustrates adding cross-type comparison operators and functions to enable comparisons between different integer types (int8, int4, int2) within the same family. The passage explains 'overloading' of operator strategy and support function numbers, emphasizing the need for distinct input data types. It stresses the importance of sort compatibility in B-tree operator families and recommends creating complete families with all operators for each data type combination. The text briefly mentions building multiple-data-type hash operator families, highlighting the need for compatible hash support functions across different data types.