From 9bd2bbef9dc092ab5fdace74e26f3afeaa62830d Mon Sep 17 00:00:00 2001 From: Tomas Vondra Date: Fri, 20 Jan 2023 22:29:53 +0100 Subject: [PATCH 2/3] add functions to extract lower/upper ramge bounds --- src/backend/catalog/system_views.sql | 19 ++- src/backend/utils/adt/rangetypes_typanalyze.c | 118 ++++++++++++++++++ src/include/catalog/pg_proc.dat | 10 ++ 3 files changed, 141 insertions(+), 6 deletions(-) diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index ccd6c7ffdb7..e3314550e32 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -259,12 +259,19 @@ CREATE VIEW pg_stats WITH (security_barrier) AS WHEN stakind5 = 6 THEN stanumbers5[1] END AS empty_range_frac, CASE - WHEN stakind1 = 7 THEN stavalues1 - WHEN stakind2 = 7 THEN stavalues2 - WHEN stakind3 = 7 THEN stavalues3 - WHEN stakind4 = 7 THEN stavalues4 - WHEN stakind5 = 7 THEN stavalues5 - END AS range_bounds_histograms + WHEN stakind1 = 7 THEN ranges_lower_bounds(stavalues1) + WHEN stakind2 = 7 THEN ranges_lower_bounds(stavalues2) + WHEN stakind3 = 7 THEN ranges_lower_bounds(stavalues3) + WHEN stakind4 = 7 THEN ranges_lower_bounds(stavalues4) + WHEN stakind5 = 7 THEN ranges_lower_bounds(stavalues5) + END AS range_lower_bounds_histograms, + CASE + WHEN stakind1 = 7 THEN ranges_upper_bounds(stavalues1) + WHEN stakind2 = 7 THEN ranges_upper_bounds(stavalues2) + WHEN stakind3 = 7 THEN ranges_upper_bounds(stavalues3) + WHEN stakind4 = 7 THEN ranges_upper_bounds(stavalues4) + WHEN stakind5 = 7 THEN ranges_upper_bounds(stavalues5) + END AS range_upper_bounds_histograms FROM pg_statistic s JOIN pg_class c ON (c.oid = s.starelid) JOIN pg_attribute a ON (c.oid = attrelid AND attnum = s.staattnum) LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace) diff --git a/src/backend/utils/adt/rangetypes_typanalyze.c b/src/backend/utils/adt/rangetypes_typanalyze.c index 86810a1a6e6..a0097282fc0 100644 --- a/src/backend/utils/adt/rangetypes_typanalyze.c +++ b/src/backend/utils/adt/rangetypes_typanalyze.c @@ -26,6 +26,8 @@ #include "catalog/pg_operator.h" #include "commands/vacuum.h" +#include "utils/array.h" +#include "utils/arrayaccess.h" #include "utils/float.h" #include "utils/fmgrprotos.h" #include "utils/lsyscache.h" @@ -427,3 +429,119 @@ compute_range_stats(VacAttrStats *stats, AnalyzeAttrFetchFunc fetchfunc, * hashtable should also go away, as it used a child memory context. */ } + +Datum +ranges_lower_bounds(PG_FUNCTION_ARGS) +{ + AnyArrayType *array = PG_GETARG_ANY_ARRAY_P(0); + int ndims = AARR_NDIM(array); + int *dims = AARR_DIMS(array); + Oid element_type = AARR_ELEMTYPE(array); + int i; + array_iter iter; + int nelems; + Datum *elems; + TypeCacheEntry *typentry; + TypeCacheEntry *typentry_element; + + /* Get information about range type; note column might be a domain */ + typentry = range_get_typcache(fcinfo, getBaseType(element_type)); + + if (typentry->typtype != TYPTYPE_RANGE) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("expected array of ranges"))); + + ndims = AARR_NDIM(array); + dims = AARR_DIMS(array); + nelems = ArrayGetNItems(ndims, dims); + + elems = (Datum *) palloc(nelems * sizeof(Datum)); + + array_iter_setup(&iter, array); + + for (i = 0; i < nelems; i++) + { + Datum itemvalue; + bool isnull; + RangeBound lower; + RangeBound upper; + bool empty; + + /* Get source element, checking for NULL */ + itemvalue = array_iter_next(&iter, &isnull, i, + typentry->typlen, typentry->typbyval, + typentry->typalign); + + Assert(!isnull); + + range_deserialize(typentry, (RangeType *) itemvalue, &lower, &upper, &empty); + elems[i] = lower.val; + } + + typentry_element = typentry->rngelemtype; + + PG_RETURN_ARRAYTYPE_P(construct_array(elems, nelems, + typentry_element->type_id, + typentry_element->typlen, + typentry_element->typbyval, + typentry_element->typalign)); +} + +Datum +ranges_upper_bounds(PG_FUNCTION_ARGS) +{ + AnyArrayType *array = PG_GETARG_ANY_ARRAY_P(0); + int ndims = AARR_NDIM(array); + int *dims = AARR_DIMS(array); + Oid element_type = AARR_ELEMTYPE(array); + int i; + array_iter iter; + int nelems; + Datum *elems; + TypeCacheEntry *typentry; + TypeCacheEntry *typentry_element; + + /* Get information about range type; note column might be a domain */ + typentry = range_get_typcache(fcinfo, getBaseType(element_type)); + + if (typentry->typtype != TYPTYPE_RANGE) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("expected array of ranges"))); + + ndims = AARR_NDIM(array); + dims = AARR_DIMS(array); + nelems = ArrayGetNItems(ndims, dims); + + elems = (Datum *) palloc(nelems * sizeof(Datum)); + + array_iter_setup(&iter, array); + + for (i = 0; i < nelems; i++) + { + Datum itemvalue; + bool isnull; + RangeBound lower; + RangeBound upper; + bool empty; + + /* Get source element, checking for NULL */ + itemvalue = array_iter_next(&iter, &isnull, i, + typentry->typlen, typentry->typbyval, + typentry->typalign); + + Assert(!isnull); + + range_deserialize(typentry, (RangeType *) itemvalue, &lower, &upper, &empty); + elems[i] = upper.val; + } + + typentry_element = typentry->rngelemtype; + + PG_RETURN_ARRAYTYPE_P(construct_array(elems, nelems, + typentry_element->type_id, + typentry_element->typlen, + typentry_element->typbyval, + typentry_element->typalign)); +} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 86eb8e8c58a..161557e4cb2 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -11891,4 +11891,14 @@ prorettype => 'bytea', proargtypes => 'pg_brin_minmax_multi_summary', prosrc => 'brin_minmax_multi_summary_send' }, +{ oid => '9693', descr => 'lower bounds of ranges', + proname => 'ranges_lower_bounds', provolatile => 's', + prorettype => 'anyarray', proargtypes => 'anyarray', + prosrc => 'ranges_lower_bounds' }, + +{ oid => '9694', descr => 'upper bounds of ranges', + proname => 'ranges_upper_bounds', provolatile => 's', + prorettype => 'anyarray', proargtypes => 'anyarray', + prosrc => 'ranges_upper_bounds' }, + ] -- 2.39.0