From 4429f6a9e3e12bb4af6e3677fbc78cd80f160252 Mon Sep 17 00:00:00 2001 From: Heikki Linnakangas Date: Thu, 3 Nov 2011 13:16:28 +0200 Subject: [PATCH] Support range data types. Selectivity estimation functions are missing for some range type operators, which is a TODO. Jeff Davis --- doc/src/sgml/catalogs.sgml | 77 + doc/src/sgml/datatype.sgml | 16 +- doc/src/sgml/extend.sgml | 22 +- doc/src/sgml/filelist.sgml | 1 + doc/src/sgml/func.sgml | 304 +++ doc/src/sgml/plpgsql.sgml | 6 +- doc/src/sgml/rangetypes.sgml | 373 +++ doc/src/sgml/ref/create_type.sgml | 61 +- doc/src/sgml/xfunc.sgml | 6 +- src/backend/catalog/Makefile | 6 +- src/backend/catalog/pg_proc.c | 63 +- src/backend/catalog/pg_range.c | 136 ++ src/backend/commands/typecmds.c | 445 +++- src/backend/executor/functions.c | 1 + src/backend/nodes/copyfuncs.c | 14 + src/backend/nodes/equalfuncs.c | 12 + src/backend/parser/gram.y | 7 + src/backend/parser/parse_coerce.c | 180 +- src/backend/tcop/utility.c | 13 + src/backend/utils/adt/Makefile | 4 +- src/backend/utils/adt/date.c | 1 - src/backend/utils/adt/pseudotypes.c | 24 + src/backend/utils/adt/rangetypes.c | 2153 +++++++++++++++++ src/backend/utils/adt/rangetypes_gist.c | 587 +++++ src/backend/utils/cache/lsyscache.c | 30 + src/backend/utils/cache/syscache.c | 12 + src/backend/utils/fmgr/funcapi.c | 115 +- src/bin/pg_dump/pg_dump.c | 184 +- src/include/catalog/catversion.h | 3 +- src/include/catalog/indexing.h | 3 + src/include/catalog/pg_amop.h | 30 + src/include/catalog/pg_amproc.h | 9 + src/include/catalog/pg_opclass.h | 3 + src/include/catalog/pg_operator.h | 39 + src/include/catalog/pg_opfamily.h | 3 + src/include/catalog/pg_proc.h | 147 ++ src/include/catalog/pg_range.h | 84 + src/include/catalog/pg_type.h | 29 +- src/include/commands/typecmds.h | 1 + src/include/nodes/nodes.h | 1 + src/include/nodes/parsenodes.h | 11 + src/include/utils/lsyscache.h | 2 + src/include/utils/rangetypes.h | 159 ++ src/include/utils/syscache.h | 1 + src/pl/plpgsql/src/pl_comp.c | 10 +- .../regress/expected/collate.linux.utf8.out | 17 + src/test/regress/expected/opr_sanity.out | 37 +- src/test/regress/expected/plpgsql.out | 14 + src/test/regress/expected/rangetypes.out | 951 ++++++++ src/test/regress/expected/sanity_check.out | 3 +- src/test/regress/expected/type_sanity.out | 2 +- src/test/regress/parallel_schedule | 2 +- src/test/regress/serial_schedule | 1 + src/test/regress/sql/collate.linux.utf8.sql | 11 + src/test/regress/sql/opr_sanity.sql | 12 +- src/test/regress/sql/plpgsql.sql | 10 + src/test/regress/sql/rangetypes.sql | 371 +++ src/test/regress/sql/type_sanity.sql | 2 +- 58 files changed, 6718 insertions(+), 103 deletions(-) create mode 100644 doc/src/sgml/rangetypes.sgml create mode 100644 src/backend/catalog/pg_range.c create mode 100644 src/backend/utils/adt/rangetypes.c create mode 100644 src/backend/utils/adt/rangetypes_gist.c create mode 100644 src/include/catalog/pg_range.h create mode 100644 src/include/utils/rangetypes.h create mode 100644 src/test/regress/expected/rangetypes.out create mode 100644 src/test/regress/sql/rangetypes.sql diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index cfecaa6931..2063812942 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -218,6 +218,11 @@ functions and procedures + + pg_range + information about range types + + pg_rewrite query rewrite rules @@ -4594,6 +4599,78 @@ + + <structname>pg_range</structname> + + + pg_range + + + + The catalog pg_range stores information about range types. + + + + <structname>pg_range</> Columns + + + + + Name + Type + References + Description + + + + + + rngtypid + oid + pg_type.oid + The type that is a range type + + + + rngsubtype + oid + pg_type.oid + Subtype of this range type, e.g. integer is the subtype of int4range + + + + rngcollation + oid + pg_collation.oid + The collation used when comparing range boundaries + + + + rngsubopc + oid + pg_opclass.oid + The operator class used when comparing range boundaries + + + + rngcanonical + regproc + pg_proc.oid + A function to convert a range into its canonical form + + + + rngsubdiff + regproc + pg_proc.oid + A function to return the distance between two lower and upper bound, as a double precision. Used for GiST support + + + +
+ + + <structname>pg_rewrite</structname> diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml index e7b3098f28..fe59a1c776 100644 --- a/doc/src/sgml/datatype.sgml +++ b/doc/src/sgml/datatype.sgml @@ -4173,6 +4173,8 @@ SET xmloption TO { DOCUMENT | CONTENT }; &rowtypes; + &rangetypes; + Object Identifier Types @@ -4443,6 +4445,10 @@ SELECT * FROM pg_attribute anyenum + + anyrange + + void @@ -4519,6 +4525,13 @@ SELECT * FROM pg_attribute ). + + anyrange + Indicates that a function accepts any range data type + (see and + ). + + anynonarray Indicates that a function accepts any non-array data type @@ -4583,7 +4596,8 @@ SELECT * FROM pg_attribute only void and record as a result type (plus trigger when the function is used as a trigger). Some also support polymorphic functions using the types anyarray, - anyelement, anyenum, and anynonarray. + anyelement, anyenum, anyrange, and + anynonarray. diff --git a/doc/src/sgml/extend.sgml b/doc/src/sgml/extend.sgml index 7079db3ed3..f3850b391e 100644 --- a/doc/src/sgml/extend.sgml +++ b/doc/src/sgml/extend.sgml @@ -198,14 +198,15 @@ - Four pseudo-types of special interest are anyelement, - anyarray, anynonarray, and anyenum, - which are collectively called polymorphic types. - Any function declared using these types is said to be - a polymorphic function. A polymorphic function can - operate on many different data types, with the specific data type(s) - being determined by the data types actually passed to it in a particular - call. + Five pseudo-types of special interest are anyelement, + anyarray, anynonarray, anyenum, + and anyrange, which are collectively + called polymorphic types. Any function declared + using these types is said to be a polymorphic + function. A polymorphic function can operate on many + different data types, with the specific data type(s) being + determined by the data types actually passed to it in a + particular call. @@ -221,6 +222,11 @@ anyelement, the actual array type in the anyarray positions must be an array whose elements are the same type appearing in the anyelement positions. + Similarly, if there are positions declared anyrange + and others declared + anyelement, the actual range type in the + anyrange positions must be a range whose subtype is + the same type appearing in the anyelement positions. anynonarray is treated exactly the same as anyelement, but adds the additional constraint that the actual type must not be an array type. diff --git a/doc/src/sgml/filelist.sgml b/doc/src/sgml/filelist.sgml index ed39e0b661..fb69415f80 100644 --- a/doc/src/sgml/filelist.sgml +++ b/doc/src/sgml/filelist.sgml @@ -25,6 +25,7 @@ + diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 2b8298c3e0..f81bb9db97 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -10457,6 +10457,310 @@ SELECT NULLIF(value, '(none)') ... + + Range Functions and Operators + + + shows the operators + available for range types. + + + + Range Operators + + + + Operator + Description + Example + Result + + + + + = + equal + int4range(1,5) = '[1,4]'::int4range + t + + + + <> + not equal + numrange(1.1,2.2) <> numrange(1.1,2.3) + t + + + + < + less than + int4range(1,10) < int4range(2,3) + t + + + + > + greater than + int4range(1,10) > int4range(1,5) + t + + + + <= + less than or equal + numrange(1.1,2.2) <= numrange(1.1,2.2) + t + + + + >= + greater than or equal + numrange(1.1,2.2) >= numrange(1.1,2.0) + t + + + + @> + contains + '[2011-01-01,2011-03-01)'::tsrange @> '2011-01-10'::timestamp + t + + + + <@ + is contained by + int4range(2,4) <@ int4range(1,7) + t + + + + && + overlap (have points in common) + int8range(3,7) && int8range(4,12) + t + + + + << + strictly left of + int8range(1,10) << int8range(100,110) + t + + + + >> + strictly right of + int8range(50,60) >> int8range(20,30) + t + + + + &< + Does not extend to the right of? + int8range(1,20) &< int8range(18,20) + t + + + + &> + Does not extend to the left of? + int8range(7,20) &> int8range(5,10) + t + + + + -|- + adjacent? + numrange(1.1,2.2) -|- numrange(2.2,3.3) + t + + + + + + Union + numrange(5,15) + numrange(10,20) + [5,20) + + + + - + Difference + int8range(5,15) - int8range(10,20) + [5,10) + + + + * + I