Contents
- Range Type Functions
- Functions
- to_range
- to_range
- to_range
- element_range_comp
- element_range_comp
- element_range_comp
- is_singleton
- is_singleton
- express_lower_bound_condition
- express_lower_bound_condition
- express_lower_bound_condition
- express_lower_bound_condition
- express_lower_bound_condition
- express_lower_bound_condition
- express_upper_bound_condition
- express_upper_bound_condition
- express_upper_bound_condition
- express_upper_bound_condition
- express_bounds_conditions
- express_bounds_conditions
- express_bounds_conditions
- express_bounds_conditions
- express_bounds_conditions
- express_bounds_conditions
- express_bounds_conditions
- Functions backported to 9.4
- range_merge
Range Type Functions
Functions
Functions available in all releases of this extension.
to_range()
A polymorphic range constructor.
sql
function to_range( low anyelement, high anyelement, bounds text, range anyrange) returns anyrange
Create a range of low,high with the bounds specified by bounds.
Parameters:
- low: The low-bound element value
- high: The high-bound element value
- bounds: The inclusivity/exclusivity bounds of the range, must be one of the following: '()', '(]', '[)', '[]'
- range: the type of the range to be created.
Example
```sql select to_range(4,5,'[]',null::int4range);
to_range
[4,6) (1 row)
select to_range('2015-01-01'::date,'2016-01-1','[)',null::daterange);
to_range
[2015-01-01,2016-01-01) (1 row)
sql
function to_range( elem anyelement, range anyrange) returns anyrange
Create a range of [elem].
Parameters:
- elem: The sole element value that can be contained the result range
- range: the type of the range to be created.
```sql select to_range('2015-01-01'::date,null::daterange);
to_range
[2015-01-01,2015-01-02) (1 row) ```
element_range_comp()
Perform a strcmp-like comparison an element and a range.
sql
function element_range_comp( element anyelement, range anyrange) returns smallint
Return 0 if the element is within the range Return -1 if the element is below the lower bound of the range. Return 1 if the element is above the upper bound of the range.
Example
```sql select element_range_comp(4,'[10,100]'::int4range);
element_range_comp
-1
(1 row)
select element_range_comp(10,'[10,100]'::int4range);
element_range_comp
0
(1 row)
select element_range_comp(110,'[10,100]'::int4range);
element_range_comp
1
(1 row) ```
is_singleton()
Determine if the range has only one possible element.
sql
function is_singleton( range anyrange) returns boolean
Returns true if the range is inclusive on both sides and the low element matches the high element.
Example
```sql select is_singleton('[4,5)'::int4range);
is_singleton
t (1 row)
select is_singleton('[4,5]'::int4range);
is_singleton
f (1 row) ```
express_lower_bound_condition
Given a range, express the simple where clause that would match the lower bound condition using range using only scalar comparisions.
sql
create function express_lower_bound_condition(range anyrange, placeholder text default 'x') returns text
Examples
```sql select express_lower_bound_condition('empty'::int4range);
express_lower_bound_condition
false (1 row)
select express_lower_bound_condition('(,)'::int4range);
express_lower_bound_condition
true (1 row)
select express_lower_bound_condition('(4,5]'::int4range);
express_lower_bound_condition
x >= '5' (1 row)
select express_lower_bound_condition('(4,5]'::int4range,'y.z');
express_lower_bound_condition
y.z >= '5' (1 row)
select express_lower_bound_condition('[4,5]'::int4range,'y.z');
express_lower_bound_condition
y.z >= '4' (1 row)
select express_lower_bound_condition('[4,5]'::int4range,format('%I.%I','my schema','my ColuMnaME'));
express_lower_bound_condition
"my schema"."my ColuMnaME" >= '4' (1 row) ```
express_upper_bound_condition
Given a range, express the simple where clause that would match the uppper bound condition using range using only scalar comparisions.
sql
create function express_upper_bound_condition(range anyrange, placeholder text default 'x') returns text
Examples
```sql select express_upper_bound_condition('empty'::int4range);
express_upper_bound_condition
false (1 row)
select express_upper_bound_condition('(,)'::int4range);
express_upper_bound_condition
true (1 row)
select express_upper_bound_condition('[4,5)'::int4range,'y.z');
express_upper_bound_condition
y.z < '5' (1 row)
select express_upper_bound_condition('[4,5]'::int4range,'y.z');
express_upper_bound_condition
y.z < '6' (1 row) ```
express_bounds_conditions
Given a range, express the simple where clause that would match the uppper and lower bound conditions using range using only scalar comparisions.
sql
create function express_upper_bounds_conditions(range anyrange, placeholder text default 'x') returns text
Examples
```sql select express_bounds_conditions('empty'::int4range);
express_bounds_conditions
false (1 row)
select express_bounds_conditions('(,)'::int4range);
express_bounds_conditions
true (1 row)
select express_bounds_conditions('(4,5]'::int4range);
express_bounds_conditions
x >= '5' and x < '6' (1 row)
select express_bounds_conditions('(4,5]'::int4range,'y.z');
express_bounds_conditions
y.z >= '5' and y.z < '6' (1 row)
select express_bounds_conditions('[4,5]'::int4range,'y.z');
express_bounds_conditions
y.z >= '4' and y.z < '6' (1 row)
select express_bounds_conditions('[4,5)'::int4range,'y.z');
express_bounds_conditions
y.z >= '4' and y.z < '5' (1 row)
select express_bounds_conditions('[4,5]'::int4range,'y.z');
express_bounds_conditions
y.z >= '4' and y.z < '6' (1 row) ```
Functions backported to 9.4
range_merge()
Attempt to back-port the function introduced in 9.5.
```sql select range_merge('[4,5]'::int4range,'[9,10]'::int4range);
range_merge
[4,11) (1 row) ```
Support
Submit issues to the GitHub issue tracker.
Author
Corey Huinker, while working at Moat
Copyright and License
Copyright (c) 2015, Moat Inc.
Permission to use, copy, modify, and distribute this software and its documentation for any purpose, without fee, and without a written agreement is hereby granted, provided that the above copyright notice and this paragraph and the following two paragraphs appear in all copies.
IN NO EVENT SHALL MOAT INC. BE LIABLE TO ANY PARTY FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF Moat, Inc. HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
MOAT INC. SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN "AS IS" BASIS, AND Moat, Inc. HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.