63.3. B-Tree Support Functions
As shown in Table 38.8 , btree defines one required and two optional support functions.
For each combination of data types that a btree operator family provides
comparison operators for, it must provide a comparison support function,
registered in
pg_amproc
with support function
number 1 and
amproclefttype
/
amprocrighttype
equal to the left and right data types for the comparison (i.e., the
same data types that the matching operators are registered with
in
pg_amop
).
The comparison function must take two non-null values
A
and
B
and
return an
int32
value that
is
<
0
,
0
,
or
>
0
when
A
<
B
,
A
=
B
,
or
A
>
B
, respectively.
A null result is disallowed: all values of the data type must be comparable.
See
src/backend/access/nbtree/nbtcompare.c
for
examples.
If the compared values are of a collatable data type, the appropriate
collation OID will be passed to the comparison support function, using
the standard
PG_GET_COLLATION()
mechanism.
Optionally, a btree operator family may provide
sort
support
function(s), registered under support function number
2. These functions allow implementing comparisons for sorting purposes
in a more efficient way than naively calling the comparison support
function. The APIs involved in this are defined in
src/include/utils/sortsupport.h
.
Optionally, a btree operator family may
provide
in_range
support function(s), registered
under support function number 3. These are not used during btree index
operations; rather, they extend the semantics of the operator family so
that it can support window clauses containing
the
RANGE
offset
PRECEDING
and
RANGE
offset
FOLLOWING
frame bound types (see
Section 4.2.8
). Fundamentally, the extra
information provided is how to add or subtract
an
offset
value in a way that is compatible
with the family's data ordering.
An
in_range
function must have the signature
in_range(val
type1,base
type1,offset
type2,sub
bool,less
bool) returns bool
val
and
base
must be
of the same type, which is one of the types supported by the operator
family (i.e., a type for which it provides an ordering).
However,
offset
could be of a different type,
which might be one otherwise unsupported by the family. An example is
that the built-in
time_ops
family provides
an
in_range
function that
has
offset
of type
interval
.
A family can provide
in_range
functions for any of
its supported types and one or more
offset
types. Each
in_range
function should be entered
in
pg_amproc
with
amproclefttype
equal to
type1
and
amprocrighttype
equal to
type2
.
The essential semantics of an
in_range
function
depend on the two boolean flag parameters. It should add or
subtract
base
and
offset
, then
compare
val
to the result, as follows:
-
if
!
sub
and!
less
, returnval
>=
(base
+
offset
) -
if
!
sub
andless
, returnval
<=
(base
+
offset
) -
if
sub
and!
less
, returnval
>=
(base
-
offset
) -
if
sub
andless
, returnval
<=
(base
-
offset
)
Before doing so, the function should check the sign
of
offset
: if it is less than zero, raise
error
ERRCODE_INVALID_PRECEDING_OR_FOLLOWING_SIZE
(22013)
with error text like
"
invalid preceding or following size in window
function
"
. (This is required by the SQL standard, although
nonstandard operator families might perhaps choose to ignore this
restriction, since there seems to be little semantic necessity for it.)
This requirement is delegated to the
in_range
function so that the core code needn't understand what
"
less than
zero
"
means for a particular data type.
An additional expectation is that
in_range
functions
should, if practical, avoid throwing an error
if
base
+
offset
or
base
-
offset
would overflow.
The correct comparison result can be determined even if that value would
be out of the data type's range. Note that if the data type includes
concepts such as
"
infinity
"
or
"
NaN
"
, extra care
may be needed to ensure that
in_range
's results agree
with the normal sort order of the operator family.
The results of the
in_range
function must be
consistent with the sort ordering imposed by the operator family.
To be precise, given any fixed values of
offset
and
sub
, then:
-
If
in_range
withless
= true is true for someval1
andbase
, it must be true for everyval2
<=
val1
with the samebase
. -
If
in_range
withless
= true is false for someval1
andbase
, it must be false for everyval2
>=
val1
with the samebase
. -
If
in_range
withless
= true is true for someval
andbase1
, it must be true for everybase2
>=
base1
with the sameval
. -
If
in_range
withless
= true is false for someval
andbase1
, it must be false for everybase2
<=
base1
with the sameval
.
Analogous statements with inverted conditions hold
when
less
= false.
If the type being ordered (
type1
) is collatable,
the appropriate collation OID will be passed to
the
in_range
function, using the standard
PG_GET_COLLATION() mechanism.
in_range
functions need not handle NULL inputs, and
typically will be marked strict.