The Interval RDBMS Pattern

The Interval Pattern

I kick-start the discussion of RDBMS patterns presenting the code required to implement the Interval Pattern.
This pattern deals with those attributes/fields in a table that define an interval. Here are certain examples.
From and To Date (Time, DateTime, Timestamp etc.,)
IP Address Ranges
Maximum and Minimum counts of Entities

Various functions and possibly triggers also accompany such attribute pairs.
Here are the functions and their implementation in PL/pgSQL
The functions are
Overlap_Interval
Distance-Size of Interval
Containment within the Interval

Here are the functions

Containment Function

DROP FUNCTION within_range(DATA_TYPE,DATA_TYPE,DATA_TYPE) CASCADE;
CREATE FUNCTION within_range(DATA_TYPE,DATA_TYPE,DATA_TYPE) RETURNS BOOLEAN AS '
DECLARE
bResult BOOLEAN;
BEGIN
bResult := false;
IF ( ( $1 >= $2 ) AND ( $1 <= $3 ) ) THEN
bResult := true;
END IF;

RETURN bResult;
END;
' LANGUAGE 'plpgsql';

Distance-Size of the Interval Function

DROP FUNCTION distance_range(DATA_TYPE,DATA_TYPE) CASCADE;
CREATE FUNCTION distance_range(DATA_TYPE,DATA_TYPE) RETURNS DATA_TYPE AS '
BEGIN
RETURN $2 - $1;
END;
' LANGUAGE 'plpgsql';

The Overlapping Interval Function

DROP FUNCTION overlap_range(DATA_TYPE,DATA_TYPE,DATA_TYPE,DATA_TYPE) CASCADE;
CREATE FUNCTION overlap_range(DATA_TYPE,DATA_TYPE,DATA_TYPE,DATA_TYPE) RETURNS BOOLEAN AS '
DECLARE
bResult BOOLEAN;
A DATA_TYPE;
B DATA_TYPE;
X DATA_TYPE;
Y DATA_TYPE;
BEGIN
IF ( $1 >= $2 ) THEN
A := $1;
B := $2;
X := $3;
Y := $4;
ELSE
X := $1;
Y := $2;
A := $3;
B := $4;
END IF;
bResult := false;
IF ( ( B >= X ) AND ( A <= X ) ) THEN
bResult := true;
END IF;

IF ( bResult AND ( A <= Y ) AND ( B >= Y) ) THEN
bResult := true;
END IF;

IF ( bResult AND ( A <= X ) AND ( B <= Y ) ) THEN
bResult := true;
END IF;

RETURN bResult;
END;
' LANGUAGE 'plpgsql';

We need to replace all occurrences of DATA_TYPE with the data type of the end points of the interval.

I will be back with implementations more such patterns in future as and when I encounter them.

posted by Kishore Sainath @ 6:58 PM