From 01f7808b3eafcae1f6077f2f61e13b4c132ccd47 Mon Sep 17 00:00:00 2001 From: Robert Haas Date: Tue, 21 Jan 2014 12:38:53 -0500 Subject: [PATCH] Add a cardinality function for arrays. Unlike our other array functions, this considers the total number of elements across all dimensions, and returns 0 rather than NULL when the array has no elements. But it seems that both of those behaviors are almost universally disliked, so hopefully that's OK. Marko Tiikkaja, reviewed by Dean Rasheed and Pavel Stehule --- doc/src/sgml/array.sgml | 13 +++++++++ doc/src/sgml/func.sgml | 14 ++++++++++ src/backend/utils/adt/arrayfuncs.c | 12 ++++++++ src/include/catalog/catversion.h | 2 +- src/include/catalog/pg_proc.h | 2 ++ src/include/utils/array.h | 1 + src/test/regress/expected/arrays.out | 42 ++++++++++++++++++++++++++++ src/test/regress/sql/arrays.sql | 8 ++++++ 8 files changed, 93 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/array.sgml b/doc/src/sgml/array.sgml index 8b36d4ab85..9ea10682a5 100644 --- a/doc/src/sgml/array.sgml +++ b/doc/src/sgml/array.sgml @@ -337,6 +337,19 @@ SELECT array_length(schedule, 1) FROM sal_emp WHERE name = 'Carol'; -------------- 2 (1 row) + + + cardinality returns the total number of elements in an + array across all dimensions. It is effectively the number of rows a call to + unnest would yield: + + +SELECT cardinality(schedule) FROM sal_emp WHERE name = 'Carol'; + + cardinality +------------- + 4 +(1 row) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index c76d357df2..53021c227d 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -11008,6 +11008,9 @@ SELECT NULLIF(value, '(none)') ... array_upper + + cardinality + string_to_array @@ -11164,6 +11167,17 @@ SELECT NULLIF(value, '(none)') ... array_upper(ARRAY[1,8,3,7], 1) 4 + + + + cardinality(anyarray) + + + int + returns the total number of elements in the array, or 0 if the array is empty + cardinality(ARRAY[[1,2],[3,4]]) + 4 + diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c index d52101663a..311d0c22f0 100644 --- a/src/backend/utils/adt/arrayfuncs.c +++ b/src/backend/utils/adt/arrayfuncs.c @@ -1739,6 +1739,18 @@ array_length(PG_FUNCTION_ARGS) PG_RETURN_INT32(result); } +/* + * array_cardinality: + * returns the total number of elements in an array + */ +Datum +array_cardinality(PG_FUNCTION_ARGS) +{ + ArrayType *v = PG_GETARG_ARRAYTYPE_P(0); + PG_RETURN_INT32(ArrayGetNItems(ARR_NDIM(v), ARR_DIMS(v))); +} + + /* * array_ref : * This routine takes an array pointer and a subscript array and returns diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index 89eb7018f6..0aca05741c 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -53,6 +53,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 201312231 +#define CATALOG_VERSION_NO 201401211 #endif diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index ab05c460c5..ad9774c285 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -840,6 +840,8 @@ DATA(insert OID = 2092 ( array_upper PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 DESCR("array upper dimension"); DATA(insert OID = 2176 ( array_length PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 23 "2277 23" _null_ _null_ _null_ _null_ array_length _null_ _null_ _null_ )); DESCR("array length"); +DATA(insert OID = 3179 ( cardinality PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 23 "2277" _null_ _null_ _null_ _null_ array_cardinality _null_ _null_ _null_ )); +DESCR("array cardinality"); DATA(insert OID = 378 ( array_append PGNSP PGUID 12 1 0 0 0 f f f f f f i 2 0 2277 "2277 2283" _null_ _null_ _null_ _null_ array_push _null_ _null_ _null_ )); DESCR("append element onto end of array"); DATA(insert OID = 379 ( array_prepend PGNSP PGUID 12 1 0 0 0 f f f f f f i 2 0 2277 "2283 2277" _null_ _null_ _null_ _null_ array_push _null_ _null_ _null_ )); diff --git a/src/include/utils/array.h b/src/include/utils/array.h index f6135a6cbb..9bbfaae85e 100644 --- a/src/include/utils/array.h +++ b/src/include/utils/array.h @@ -204,6 +204,7 @@ extern Datum array_dims(PG_FUNCTION_ARGS); extern Datum array_lower(PG_FUNCTION_ARGS); extern Datum array_upper(PG_FUNCTION_ARGS); extern Datum array_length(PG_FUNCTION_ARGS); +extern Datum array_cardinality(PG_FUNCTION_ARGS); extern Datum array_larger(PG_FUNCTION_ARGS); extern Datum array_smaller(PG_FUNCTION_ARGS); extern Datum generate_subscripts(PG_FUNCTION_ARGS); diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out index 23b3902017..e1b9d7f0a8 100644 --- a/src/test/regress/expected/arrays.out +++ b/src/test/regress/expected/arrays.out @@ -1455,6 +1455,48 @@ select array_length(array[[1,2,3], [4,5,6]], 3); (1 row) +select cardinality(NULL::int[]); + cardinality +------------- + +(1 row) + +select cardinality('{}'::int[]); + cardinality +------------- + 0 +(1 row) + +select cardinality(array[1,2,3]); + cardinality +------------- + 3 +(1 row) + +select cardinality('[2:4]={5,6,7}'::int[]); + cardinality +------------- + 3 +(1 row) + +select cardinality('{{1,2}}'::int[]); + cardinality +------------- + 2 +(1 row) + +select cardinality('{{1,2},{3,4},{5,6}}'::int[]); + cardinality +------------- + 6 +(1 row) + +select cardinality('{{{1}},{{2,3},{3,4}}}'::int[]); + cardinality +------------- + 8 +(1 row) + select array_agg(unique1) from (select unique1 from tenk1 where unique1 < 15 order by unique1) ss; array_agg -------------------------------------- diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql index e4f9f316ce..64630d9ab7 100644 --- a/src/test/regress/sql/arrays.sql +++ b/src/test/regress/sql/arrays.sql @@ -419,6 +419,14 @@ select array_length(array[[1,2,3], [4,5,6]], 1); select array_length(array[[1,2,3], [4,5,6]], 2); select array_length(array[[1,2,3], [4,5,6]], 3); +select cardinality(NULL::int[]); +select cardinality('{}'::int[]); +select cardinality(array[1,2,3]); +select cardinality('[2:4]={5,6,7}'::int[]); +select cardinality('{{1,2}}'::int[]); +select cardinality('{{1,2},{3,4},{5,6}}'::int[]); +select cardinality('{{{1}},{{2,3},{3,4}}}'::int[]); + select array_agg(unique1) from (select unique1 from tenk1 where unique1 < 15 order by unique1) ss; select array_agg(ten) from (select ten from tenk1 where unique1 < 15 order by unique1) ss; select array_agg(nullif(ten, 4)) from (select ten from tenk1 where unique1 < 15 order by unique1) ss; -- 2.40.0