TPSQLRangeField encapsulates all built-in PostgreSQL range types: int4range, int8range, numrange, tsrange, tstzrange and daterange.
type
TPSQLRangeBoundState = (rbsExclusive, rbsInclusive, rbsInfinite);
TPSQLRangeBound = packed record
public
State: TPSQLRangeBoundState;
property AsInteger: integer;
property AsFloat: double;
property AsLargeInt: Int64;
property AsDateTime: TDateTime;
end;
TPSQLRange = packed record
public
LowerBound: TPSQLRangeBound;
UpperBound: TPSQLRangeBound;
property Empty: boolean;
procedure SetEmpty;
constructor Create(const Value: string; const RangeType: cardinal);
end;
TPSQLRangeField = class(TNumericField)
public
function IsDiscrete: boolean; virtual;
function IsEmpty: boolean; virtual;
property Value: TPSQLRange;
end;
Description:
Information about range value is stored in the TPSQLRange record by storing values for each bound using TPSQLRangeBound record. Depending on the underlying table field type developer may access bound values using appropriate methods AsInteger, AsFloat, AsDateTime etc.
Inclusive and Exclusive Bounds
Every non-empty range has two bounds, the lower bound and the upper bound. All points between these values are included in the range. An inclusive bound means that the boundary point itself is included in the range as well, while an exclusive bound means that the boundary point is not included in the range.
In the text form of a range, an inclusive lower bound is represented by "[" while an exclusive lower bound is represented by "(". Likewise, an inclusive upper bound is represented by "]", while an exclusive upper bound is represented by ")".
Infinite (Unbounded) Ranges
The lower bound of a range can be omitted, meaning that all points less than the upper bound are included in the range. Likewise, if the upper bound of the range is omitted, then all points greater than the lower bound are included in the range. If both lower and upper bounds are omitted, all values of the element type are considered to be in the range.
This is equivalent to considering that the lower bound is "minus infinity", or the upper bound is "plus infinity", respectively. But note that these infinite values are never values of the range's element type, and can never be part of the range. (So there is no such thing as an inclusive infinite bound - if you try to write one, it will automatically be converted to an exclusive bound.)
Also, some element types have a notion of "infinity", but that is just another value so far as the range type mechanisms are concerned. For example, in timestamp ranges, [today,] means the same thing as [today,). But [today,infinity] means something different from [today,infinity) - the latter excludes the special timestamp value infinity.
Use TPSQLRangeBound.State property to determine if the bound is inclusive, exclusive or infinite.
Discrete Range Types
A discrete range is one whose element type has a well-defined "step", such as integer or date. In these types two elements can be said to be adjacent, when there are no valid values between them. This contrasts with continuous ranges, where it's always (or almost always) possible to identify other element values between two given values. For example, a range over the numeric type is continuous, as is a range over timestamp. (Even though timestamp has limited precision, and so could theoretically be treated as discrete, it's better to consider it continuous since the step size is normally not of interest.)
Another way to think about a discrete range type is that there is a clear idea of a "next" or "previous" value for each element value. Knowing that, it is possible to convert between inclusive and exclusive representations of a range's bounds, by choosing the next or previous element value instead of the one originally given. For example, in an integer range type [4,8] and (3,9) denote the same set of values; but this would not be so for a range over numeric.
A discrete range type should have a canonicalization function that is aware of the desired step size for the element type. The canonicalization function is charged with converting equivalent values of the range type to have identical representations, in particular consistently inclusive or exclusive bounds. If a canonicalization function is not specified, then ranges with different formatting will always be treated as unequal, even though they might represent the same set of values in reality.
The built-in range types int4range, int8range, and daterange all use a canonical form that includes the lower bound and excludes the upper bound; that is, [).
Use TPSQLRangeField.IsDiscrete function to determine if the range is discrete.
Empty Range
There is special empty value for range type. It is pretty much like empty string value for text types. One should clearly distinguish this value from NULL state of the field.
SELECT '[4,4)'::int4range; -- includes no points (and will be normalized to 'empty')
Use TPSQLRangeField.IsEmpty to check if the range value is empty, and TPSQLRangeField.SetEmpty to make range value empty.