On 1/12/14, 5:53 AM, I wrote:
On 1/9/14, 2:57 PM, Dean Rasheed wrote:
How it should behave for multi-dimensional arrays is less clear, but
I'd argue that it should return the total number of elements, i.e.
cardinality('{{1,2},{3,4}}'::int[][]) = 4. That would make it
consistent with the choices we've already made for unnest() and
ordinality:
- cardinality(foo) = (select count(*) from unnest(foo)).
- unnest with ordinality would always result in ordinals in the range
[1, cardinality].
Ignoring my proposal, this seems like the most reasonable option. I'll
send an updated patch along these lines.
Here's the patch as promised. Thoughts?
Regards,
Marko Tiikkaja
*** a/doc/src/sgml/array.sgml
--- b/doc/src/sgml/array.sgml
***************
*** 338,343 **** SELECT array_length(schedule, 1) FROM sal_emp WHERE name =
'Carol';
--- 338,356 ----
2
(1 row)
</programlisting>
+
+ <function>cardinality</function> returns the total number of elements in an
+ array across all dimensions. It is effectively the number of rows a call to
+ <function>unnest</function> would yield:
+
+ <programlisting>
+ SELECT cardinality(schedule) FROM sal_emp WHERE name = 'Carol';
+
+ cardinality
+ -------------
+ 4
+ (1 row)
+ </programlisting>
</para>
</sect2>
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
***************
*** 11009,11014 **** SELECT NULLIF(value, '(none)') ...
--- 11009,11017 ----
<primary>array_upper</primary>
</indexterm>
<indexterm>
+ <primary>cardinality</primary>
+ </indexterm>
+ <indexterm>
<primary>string_to_array</primary>
</indexterm>
<indexterm>
***************
*** 11167,11172 **** SELECT NULLIF(value, '(none)') ...
--- 11170,11186 ----
<row>
<entry>
<literal>
+ <function>cardinality</function>(<type>anyarray</type>)
+ </literal>
+ </entry>
+ <entry><type>int</type></entry>
+ <entry>returns the total number of elements in the array, or 0 if the
array is empty</entry>
+ <entry><literal>cardinality(ARRAY[[1,2],[3,4]])</literal></entry>
+ <entry><literal>4</literal></entry>
+ </row>
+ <row>
+ <entry>
+ <literal>
<function>string_to_array</function>(<type>text</type>,
<type>text</type> <optional>, <type>text</type></optional>)
</literal>
</entry>
*** a/src/backend/utils/adt/arrayfuncs.c
--- b/src/backend/utils/adt/arrayfuncs.c
***************
*** 1740,1745 **** array_length(PG_FUNCTION_ARGS)
--- 1740,1766 ----
}
/*
+ * array_cardinality:
+ * returns the total number of elements in an array
+ */
+ Datum
+ array_cardinality(PG_FUNCTION_ARGS)
+ {
+ ArrayType *v = PG_GETARG_ARRAYTYPE_P(0);
+ int *dimv;
+ int i;
+ int cardinality;
+
+ dimv = ARR_DIMS(v);
+ cardinality = 1;
+ for (i = 0; i < ARR_NDIM(v); i++)
+ cardinality *= dimv[i];
+
+ PG_RETURN_INT32(cardinality);
+ }
+
+
+ /*
* array_ref :
* This routine takes an array pointer and a subscript array and returns
* the referenced item as a Datum. Note that for a pass-by-reference
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
***************
*** 840,845 **** DATA(insert OID = 2092 ( array_upper PGNSP PGUID 12 1 0 0
0 f f f f t f i 2
--- 840,847 ----
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_ ));
*** a/src/include/utils/array.h
--- b/src/include/utils/array.h
***************
*** 204,209 **** extern Datum array_dims(PG_FUNCTION_ARGS);
--- 204,210 ----
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);
*** a/src/test/regress/expected/arrays.out
--- b/src/test/regress/expected/arrays.out
***************
*** 1455,1460 **** select array_length(array[[1,2,3], [4,5,6]], 3);
--- 1455,1502 ----
(1 row)
+ select cardinality(NULL::int[]);
+ cardinality
+ -------------
+
+ (1 row)
+
+ select cardinality('{}'::int[]);
+ cardinality
+ -------------
+ 1
+ (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
--------------------------------------
*** a/src/test/regress/sql/arrays.sql
--- b/src/test/regress/sql/arrays.sql
***************
*** 419,424 **** select array_length(array[[1,2,3], [4,5,6]], 1);
--- 419,432 ----
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;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
