Re: [HACKERS] array_length(anyarray)
On Sun, Jan 19, 2014 at 1:41 AM, Pavel Stehule pavel.steh...@gmail.com wrote: 2014/1/19 Marko Tiikkaja ma...@joh.to On 1/19/14, 12:21 AM, Pavel Stehule wrote: I checked it and I got a small issue bash-4.1$ patch -p1 cardinality.patch (Stripping trailing CRs from patch.) not sure about source of this problem. I can't reproduce the problem. In fact, I don't see a single CR byte in the patch file on my disk, the file my email clients claims to have sent or a copy of the file I downloaded from the archives. Are you sure this isn't a problem on your end? It can be problem on my side - some strange combination of mime type. I seen this issue before. I will recheck it tomorrow from other computer. Doesn't matter anyway. Patch needing to strip trailing CRs doesn't cause any issue. I got the same message, BTW; maybe some kind of gmail weirdness. As it turns out, a function called cardinality() was added in 2009 and ripped back out again. But the one that was committed back then had funny semantics that people weren't sure about, and people seemed to think it should behave like this one does. So I've gone ahead and committed this, crossing my fingers that we won't have to rip it back out again. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] array_length(anyarray)
On 1/21/14, 6:42 PM, Robert Haas wrote: On Sun, Jan 19, 2014 at 1:41 AM, Pavel Stehule pavel.steh...@gmail.com wrote: It can be problem on my side - some strange combination of mime type. I seen this issue before. I will recheck it tomorrow from other computer. Doesn't matter anyway. Patch needing to strip trailing CRs doesn't cause any issue. I got the same message, BTW; maybe some kind of gmail weirdness. Interesting. As it turns out, a function called cardinality() was added in 2009 and ripped back out again. But the one that was committed back then had funny semantics that people weren't sure about, and people seemed to think it should behave like this one does. So I've gone ahead and committed this, crossing my fingers that we won't have to rip it back out again. Thanks! I'll keep my fingers crossed as well. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] array_length(anyarray)
On 19 January 2014 11:43, Marko Tiikkaja ma...@joh.to wrote: New version attached, without the doc change. This looks good to me. - applies cleanly. - compiles with no warnings. - passes a sensible set of new regression tests. - implements the agreed behaviour, per SQL spec. - I can't think of any corner cases to break it. I think this is ready for committer, although I would also like to see the doc changes to make the table of array function descriptions a bit more explicit about corner cases. Also, does this mean that we can now claim full support for SQL feature S091 Basic array support? Regards, Dean -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] array_length(anyarray)
On 1/20/14 2:29 PM, Dean Rasheed wrote: I think this is ready for committer Thanks! ... although I would also like to see the doc changes to make the table of array function descriptions a bit more explicit about corner cases. Hmm. I completely missed the fact that unnest() already uses a structure similar to yours. It looks like e.g. window functions do the same, but JSON functions all have proper capitalization and periods, and some others capitalize but omit periods. I could submit a separate patch to describe array functions in a bit more detail, unless you wanted to do that? I'm not planning on fixing the inconsistencies, though, despite them annoying me. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] array_length(anyarray)
On 20 January 2014 13:47, Marko Tiikkaja ma...@joh.to wrote: On 1/20/14 2:29 PM, Dean Rasheed wrote: I think this is ready for committer Thanks! ... although I would also like to see the doc changes to make the table of array function descriptions a bit more explicit about corner cases. Hmm. I completely missed the fact that unnest() already uses a structure similar to yours. It looks like e.g. window functions do the same, but JSON functions all have proper capitalization and periods, and some others capitalize but omit periods. I could submit a separate patch to describe array functions in a bit more detail, Yes please. I think that would be helpful. I'm not planning on fixing the inconsistencies, though, despite them annoying me. To be honest, I hadn't even noticed those inconsistencies. The main thing is to alert new users to the fact that empty arrays behave in a rather odd way for a couple of those functions. Regards, Dean -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] array_length(anyarray)
On 18 January 2014 03:07, Marko Tiikkaja ma...@joh.to wrote: 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? A couple of points: The answer for empty (zero dimensional) arrays is wrong --- you need special case handling for this case to return 0. In fact why not simply use ArrayGetNItems()? In the docs, in the table of array functions, I think it would probably be useful to make the entry for array_length say see also cardinality, otherwise people might just stop reading there. I suspect that in over 90% of cases, cardinality will be the more appropriate function to use rather than array_length. Regards, Dean -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] array_length(anyarray)
On 1/19/14, 9:12 AM, Dean Rasheed wrote: On 18 January 2014 03:07, Marko Tiikkaja ma...@joh.to wrote: Here's the patch as promised. Thoughts? A couple of points: The answer for empty (zero dimensional) arrays is wrong --- you need special case handling for this case to return 0. How embarrassing. I don't know why I removed that check or how I didn't catch the clearly wrong answer in the test output. In fact why not simply use ArrayGetNItems()? Even better. Changed. In the docs, in the table of array functions, I think it would probably be useful to make the entry for array_length say see also cardinality, otherwise people might just stop reading there. I suspect that in over 90% of cases, cardinality will be the more appropriate function to use rather than array_length. I don't see this as a huge improvement, but even worse, I don't see a way to naturally fit it into the description. New version attached, without the doc change. 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 + + functioncardinality/function returns the total number of elements in an + array across all dimensions. It is effectively the number of rows a call to + functionunnest/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 primaryarray_upper/primary /indexterm indexterm + primarycardinality/primary + /indexterm + indexterm primarystring_to_array/primary /indexterm indexterm *** *** 11167,11172 SELECT NULLIF(value, '(none)') ... --- 11170,11186 row entry literal + functioncardinality/function(typeanyarray/type) + /literal + /entry + entrytypeint/type/entry + entryreturns the total number of elements in the array, or 0 if the array is empty/entry + entryliteralcardinality(ARRAY[[1,2],[3,4]])/literal/entry + entryliteral4/literal/entry +/row +row + entry + literal functionstring_to_array/function(typetext/type, typetext/type optional, typetext/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,1757 } /* + * 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 * 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 ( cardinalityPGNSP 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_appendPGNSP 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
Re: [HACKERS] array_length(anyarray)
On 19 January 2014 11:43, Marko Tiikkaja ma...@joh.to wrote: On 1/19/14, 9:12 AM, Dean Rasheed wrote: On 18 January 2014 03:07, Marko Tiikkaja ma...@joh.to wrote: Here's the patch as promised. Thoughts? A couple of points: The answer for empty (zero dimensional) arrays is wrong --- you need special case handling for this case to return 0. How embarrassing. I don't know why I removed that check or how I didn't catch the clearly wrong answer in the test output. In fact why not simply use ArrayGetNItems()? Even better. Changed. In the docs, in the table of array functions, I think it would probably be useful to make the entry for array_length say see also cardinality, otherwise people might just stop reading there. I suspect that in over 90% of cases, cardinality will be the more appropriate function to use rather than array_length. I don't see this as a huge improvement, but even worse, I don't see a way to naturally fit it into the description. Hmm. Looking at that page in the docs, it currently doesn't even mention that array_length returns NULL for empty arrays, or more generally for arrays that don't have the requested dimension. To someone unfamiliar with postgresql arrays, that could lead to a nasty surprise. How about having the array_length docs say something like returns the length of the requested array dimension, or NULL if the array is empty or does not have the requested dimension. To get the total number of array elements across all dimensions, use functioncardinality/. If we did that, we should probably also add the or NULL if the array is empty or does not have the requested dimension clause to the array_upper and array_lower docs, and or NULL if the array is empty to the array_dims and array_ndims docs. That might seem overly pedantic, but it's quite annoying when API documentation doesn't fully specify the behaviour, and you're forced to use trial-and-error to find out how the functions behave. Regards, Dean -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] array_length(anyarray)
On 1/19/14, 2:12 PM, Dean Rasheed wrote: That might seem overly pedantic, but it's quite annoying when API documentation doesn't fully specify the behaviour, and you're forced to use trial-and-error to find out how the functions behave. For what it's worth, I was thinking the same thing when I was looking at that table. Nearly *all* of them are completely inadequate at explaining the finer details, and the user has to experiment to figure out what actually happens. I seem to recall other similar examples in our documentation for functions. Personally I would like to see this fixed for all functions, not just array functions. But I think that should be a separate patch. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] array_length(anyarray)
Hello I checked it and I got a small issue bash-4.1$ patch -p1 cardinality.patch (Stripping trailing CRs from patch.) patching file doc/src/sgml/array.sgml (Stripping trailing CRs from patch.) patching file doc/src/sgml/func.sgml (Stripping trailing CRs from patch.) patching file src/backend/utils/adt/arrayfuncs.c (Stripping trailing CRs from patch.) patching file src/include/catalog/pg_proc.h (Stripping trailing CRs from patch.) patching file src/include/utils/array.h (Stripping trailing CRs from patch.) patching file src/test/regress/expected/arrays.out (Stripping trailing CRs from patch.) patching file src/test/regress/sql/arrays.sql not sure about source of this problem. Function works correctly and I would this feature Regards Pavel 2014/1/18 Marko Tiikkaja ma...@joh.to 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
Re: [HACKERS] array_length(anyarray)
On 1/19/14, 12:21 AM, Pavel Stehule wrote: I checked it and I got a small issue bash-4.1$ patch -p1 cardinality.patch (Stripping trailing CRs from patch.) not sure about source of this problem. I can't reproduce the problem. In fact, I don't see a single CR byte in the patch file on my disk, the file my email clients claims to have sent or a copy of the file I downloaded from the archives. Are you sure this isn't a problem on your end? Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] array_length(anyarray)
2014/1/19 Marko Tiikkaja ma...@joh.to On 1/19/14, 12:21 AM, Pavel Stehule wrote: I checked it and I got a small issue bash-4.1$ patch -p1 cardinality.patch (Stripping trailing CRs from patch.) not sure about source of this problem. I can't reproduce the problem. In fact, I don't see a single CR byte in the patch file on my disk, the file my email clients claims to have sent or a copy of the file I downloaded from the archives. Are you sure this isn't a problem on your end? It can be problem on my side - some strange combination of mime type. I seen this issue before. I will recheck it tomorrow from other computer. Regards Pavel Regards, Marko Tiikkaja
Re: [HACKERS] array_length(anyarray)
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 + + functioncardinality/function returns the total number of elements in an + array across all dimensions. It is effectively the number of rows a call to + functionunnest/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 primaryarray_upper/primary /indexterm indexterm + primarycardinality/primary + /indexterm + indexterm primarystring_to_array/primary /indexterm indexterm *** *** 11167,11172 SELECT NULLIF(value, '(none)') ... --- 11170,11186 row entry literal + functioncardinality/function(typeanyarray/type) + /literal + /entry + entrytypeint/type/entry + entryreturns the total number of elements in the array, or 0 if the array is empty/entry + entryliteralcardinality(ARRAY[[1,2],[3,4]])/literal/entry + entryliteral4/literal/entry +/row +row + entry + literal functionstring_to_array/function(typetext/type, typetext/type optional, typetext/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 ( cardinalityPGNSP 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_appendPGNSP 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
Re: [HACKERS] array_length(anyarray)
On Jan10, 2014, at 15:10 , Merlin Moncure mmonc...@gmail.com wrote: On Fri, Jan 10, 2014 at 6:00 AM, Florian Pflug f...@phlo.org wrote: On Jan10, 2014, at 11:00 , Merlin Moncure mmonc...@gmail.com wrote: On Fri, Jan 10, 2014 at 3:52 AM, Marko Tiikkaja ma...@joh.to wrote: On 1/10/14, 10:41 AM, Merlin Moncure wrote: What's needed for better iteration support (IMO) is a function that does what unnest does but returns an array on indexes (one per dimsension) -- a generalization of the _pg_expandarray function. Lets' say 'unnest_dims'. So unnest_dims('{{1,2},{3,4}}'::int[]) would return VALUES (1, '{1,2}'::int[]), (2, '{3,4}'::int[])? If so, then yes, that's a functionality I've considered us to have been missing for a long time. not quite. it returns int[], anyelement: so, using your example, you'd get: [1,1], 1 [1,2], 2 [2,1], 3 [2,2], 4 Now that we have WITH ORDINALITY, it'd be sufficient to have a variant of array_dims() that returns int[][] instead of text, say array_dimsarray(). Your unnest_dims could then be written as unnest(array_dimsarray(array)) with ordinality hm, not quite following that. maybe an example? my issue with 'WITH ORDINALITY' (while it's pretty neat) is that it doesn't give you the dimension coordinate of each datum so you can't really use it to slice. with unnest_dims(), you an slice, say via: Sorry, I misunderstood what you were proposing. I though you intended unnest_dims to returns one row per dimension, containing the index and bounds of that dimension. And yeah, that fact your your mail showed unnest_dims returning *4* rows for a *2*-dimensional array should have tipped me off. best regards, Florian Pflug -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] array_length(anyarray)
On 1/9/14, 2:57 PM, Dean Rasheed wrote: Yes, this should just return the number of elements, and 0 for an empty array. 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. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] array_length(anyarray)
2014/1/12 Marko Tiikkaja ma...@joh.to On 1/9/14, 2:57 PM, Dean Rasheed wrote: Yes, this should just return the number of elements, and 0 for an empty array. 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. +1 Pavel Regards, Marko Tiikkaja
Re: [HACKERS] array_length(anyarray)
On 10 January 2014 00:36, Marko Tiikkaja ma...@joh.to wrote: On 1/10/14, 1:20 AM, Merlin Moncure wrote: I'm piling on: it's not clear at all to me why you've special cased this to lower_bound=1. First of all, there are other reasons to check length than iteration. Yes, I agree. A length function that returned 0 for empty arrays would be far from useless. Can you point me to some examples? The example I see all the time is code like if array_length(nodes, 1) 5 then ... do something ... then you realise (or not as the case may be) that this doesn't work for empty arrays, and have to remember to wrap it in a coalesce call. Simply being able to write if cardinality(nodes) 5 then ... do something ... is not just shorter, easier to type and easier to read, it is far less likely to be the source of subtle bugs. Regards, Dean -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] array_length(anyarray)
On 1/10/14, 9:04 AM, Dean Rasheed wrote: On 10 January 2014 00:36, Marko Tiikkaja ma...@joh.to wrote: Can you point me to some examples? The example I see all the time is code like if array_length(nodes, 1) 5 then ... do something ... then you realise (or not as the case may be) that this doesn't work for empty arrays, and have to remember to wrap it in a coalesce call. Simply being able to write if cardinality(nodes) 5 then ... do something ... is not just shorter, easier to type and easier to read, it is far less likely to be the source of subtle bugs But this is what I don't understand: why do you care whether there's less than 5 elements in the array, but you don't care about how they're organized? '[2:3]={1,2}'::int[] and '{{1},{2}}'::int[] both give the same result when unnest()ed, sure, but why do you want to accept such crap as input if you just want a list of elements? I guess what I truly want is a less generic type that's like an array, but always one-dimensional with a lower bound of 1. There's too much garbage that can be passed to a function taking an array as an input right now. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] array_length(anyarray)
On Fri, Jan 10, 2014 at 2:04 AM, Dean Rasheed dean.a.rash...@gmail.com wrote: On 10 January 2014 00:36, Marko Tiikkaja ma...@joh.to wrote: On 1/10/14, 1:20 AM, Merlin Moncure wrote: I'm piling on: it's not clear at all to me why you've special cased this to lower_bound=1. First of all, there are other reasons to check length than iteration. Yes, I agree. A length function that returned 0 for empty arrays would be far from useless. Can you point me to some examples? The example I see all the time is code like if array_length(nodes, 1) 5 then ... do something ... then you realise (or not as the case may be) that this doesn't work for empty arrays, and have to remember to wrap it in a coalesce call. Simply being able to write if cardinality(nodes) 5 then ... do something ... is not just shorter, easier to type and easier to read, it is far less likely to be the source of subtle bugs. right -- exactly. or, 'ORDER BY cardinatility(nodes)', etc etc. Furthermore, we already have pretty good support for iteration with arrays via unnest(). What's needed for better iteration support (IMO) is a function that does what unnest does but returns an array on indexes (one per dimsension) -- a generalization of the _pg_expandarray function. Lets' say 'unnest_dims'. 'unnest_dims' is non-trivial to code in user land while 'array_length' is an extremely trivial wrapper to array_upper(). cardinality() (which is much better name for the function IMSNHO) gives a*b*c values say for a 3d array also does something non-trivial *particularly in the case of offset arrays*. On Fri, Jan 10, 2014 at 3:36 AM, Marko Tiikkaja ma...@joh.to wrote: I guess what I truly want is a less generic type that's like an array, but always one-dimensional with a lower bound of 1. Your function would be the only one in the array API that implemented special behaviors like that. That's suggests to me that the less generic function belongs in user land, not in the core array API. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] array_length(anyarray)
On 1/10/14, 10:41 AM, Merlin Moncure wrote: What's needed for better iteration support (IMO) is a function that does what unnest does but returns an array on indexes (one per dimsension) -- a generalization of the _pg_expandarray function. Lets' say 'unnest_dims'. So unnest_dims('{{1,2},{3,4}}'::int[]) would return VALUES (1, '{1,2}'::int[]), (2, '{3,4}'::int[])? If so, then yes, that's a functionality I've considered us to have been missing for a long time. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] array_length(anyarray)
On Fri, Jan 10, 2014 at 3:52 AM, Marko Tiikkaja ma...@joh.to wrote: On 1/10/14, 10:41 AM, Merlin Moncure wrote: What's needed for better iteration support (IMO) is a function that does what unnest does but returns an array on indexes (one per dimsension) -- a generalization of the _pg_expandarray function. Lets' say 'unnest_dims'. So unnest_dims('{{1,2},{3,4}}'::int[]) would return VALUES (1, '{1,2}'::int[]), (2, '{3,4}'::int[])? If so, then yes, that's a functionality I've considered us to have been missing for a long time. not quite. it returns int[], anyelement: so, using your example, you'd get: [1,1], 1 [1,2], 2 [2,1], 3 [2,2], 4 like unnest() it would fully decompose the array do individual elements. what you have above slices the array which is useful,but probably shouldn't live under the 'unnest' name -- perhaps 'slice'. Pavel added it to pl/pgsql under the FOREACH syntax (FYI). merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] array_length(anyarray)
On Jan10, 2014, at 11:00 , Merlin Moncure mmonc...@gmail.com wrote: On Fri, Jan 10, 2014 at 3:52 AM, Marko Tiikkaja ma...@joh.to wrote: On 1/10/14, 10:41 AM, Merlin Moncure wrote: What's needed for better iteration support (IMO) is a function that does what unnest does but returns an array on indexes (one per dimsension) -- a generalization of the _pg_expandarray function. Lets' say 'unnest_dims'. So unnest_dims('{{1,2},{3,4}}'::int[]) would return VALUES (1, '{1,2}'::int[]), (2, '{3,4}'::int[])? If so, then yes, that's a functionality I've considered us to have been missing for a long time. not quite. it returns int[], anyelement: so, using your example, you'd get: [1,1], 1 [1,2], 2 [2,1], 3 [2,2], 4 Now that we have WITH ORDINALITY, it'd be sufficient to have a variant of array_dims() that returns int[][] instead of text, say array_dimsarray(). Your unnest_dims could then be written as unnest(array_dimsarray(array)) with ordinality best regards, florian pflug -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] array_length(anyarray)
On Fri, Jan 10, 2014 at 6:00 AM, Florian Pflug f...@phlo.org wrote: On Jan10, 2014, at 11:00 , Merlin Moncure mmonc...@gmail.com wrote: On Fri, Jan 10, 2014 at 3:52 AM, Marko Tiikkaja ma...@joh.to wrote: On 1/10/14, 10:41 AM, Merlin Moncure wrote: What's needed for better iteration support (IMO) is a function that does what unnest does but returns an array on indexes (one per dimsension) -- a generalization of the _pg_expandarray function. Lets' say 'unnest_dims'. So unnest_dims('{{1,2},{3,4}}'::int[]) would return VALUES (1, '{1,2}'::int[]), (2, '{3,4}'::int[])? If so, then yes, that's a functionality I've considered us to have been missing for a long time. not quite. it returns int[], anyelement: so, using your example, you'd get: [1,1], 1 [1,2], 2 [2,1], 3 [2,2], 4 Now that we have WITH ORDINALITY, it'd be sufficient to have a variant of array_dims() that returns int[][] instead of text, say array_dimsarray(). Your unnest_dims could then be written as unnest(array_dimsarray(array)) with ordinality hm, not quite following that. maybe an example? my issue with 'WITH ORDINALITY' (while it's pretty neat) is that it doesn't give you the dimension coordinate of each datum so you can't really use it to slice. with unnest_dims(), you an slice, say via: select array_agg(value) from (unnest_dims('{{1,2},{3,4}}'::int[]) group by dims[1]; or select array_agg(value) from (unnest_dims('{{1,2},{3,4}}'::int[]) where dims[1] = 2; not super elegant, but good enough for most uses I think. anyways, getting back on topic, the question on the table is cardinality() vs array_length, right? merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] array_length(anyarray)
On 19 December 2013 08:05, Pavel Stehule pavel.steh...@gmail.com wrote: 2013/12/19 David Fetter da...@fetter.org On Wed, Dec 18, 2013 at 09:27:54PM +0100, Marko Tiikkaja wrote: Hi, Attached is a patch to add support for array_length(anyarray), which only works for one-dimensional arrays, returns 0 for empty arrays and complains if the array's lower bound isn't 1. In other words, does the right thing when used with the arrays people use 99% of the time. +1 for adding this. +1 I think that having 2 functions called array_length() that each behave differently for empty arrays would just lead to confusion. The SQL standard defines a function called cardinality() that returns the number of elements of a collection (array or multiset), so why don't we call it that? length should be irrelevant to fact so array starts from 1, 0 or anything else Yes, this should just return the number of elements, and 0 for an empty array. 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]. Regards, Dean -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] array_length(anyarray)
On Jan9, 2014, at 14:57 , Dean Rasheed dean.a.rash...@gmail.com wrote: On 19 December 2013 08:05, Pavel Stehule pavel.steh...@gmail.com wrote: length should be irrelevant to fact so array starts from 1, 0 or anything else Yes, this should just return the number of elements, and 0 for an empty array. +1. Anything that complains about arrays whose lower bound isn't 1 really needs a *way* less generic name than array_length(). 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]. +1 best regards, Florian Pflug -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] array_length(anyarray)
On 1/9/14 5:44 PM, Florian Pflug wrote: On Jan9, 2014, at 14:57 , Dean Rasheed dean.a.rash...@gmail.com wrote: On 19 December 2013 08:05, Pavel Stehule pavel.steh...@gmail.com wrote: length should be irrelevant to fact so array starts from 1, 0 or anything else Yes, this should just return the number of elements, and 0 for an empty array. +1. Anything that complains about arrays whose lower bound isn't 1 really needs a *way* less generic name than array_length(). Problem is, if you're operating on an array which could have a lower bound that isn't 1, why would you look at the length in the first place? You can't access any elements by index, you'd need to look at array_lower(). You can't iterate over the array by index, you'd need to do array_lower() .. array_lower() + array_length(), which doesn't make sense. And then there's the myriad of stuff you can do with unnest() without actually having to look at the length. Same goes for multi-dimensional arrays: you have even less things you can do there with only a length. So if we give up these constraints, we also make this function completely useless. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] array_length(anyarray)
On 1/9/14, 11:08 AM, Marko Tiikkaja wrote: On 1/9/14 5:44 PM, Florian Pflug wrote: On Jan9, 2014, at 14:57 , Dean Rasheed dean.a.rash...@gmail.com wrote: On 19 December 2013 08:05, Pavel Stehule pavel.steh...@gmail.com wrote: length should be irrelevant to fact so array starts from 1, 0 or anything else Yes, this should just return the number of elements, and 0 for an empty array. +1. Anything that complains about arrays whose lower bound isn't 1 really needs a *way* less generic name than array_length(). Problem is, if you're operating on an array which could have a lower bound that isn't 1, why would you look at the length in the first place? You can't access any elements by index, you'd need to look at array_lower(). You can't iterate over the array by index, you'd need to do array_lower() .. array_lower() + array_length(), which doesn't make sense. And then there's the myriad of stuff you can do with unnest() without actually having to look at the length. Same goes for multi-dimensional arrays: you have even less things you can do there with only a length. So if we give up these constraints, we also make this function completely useless. I'm generally opposed to creating code that doesn't support the full featureset of something (in this case, array_lower()1). But in this case I hope we can all agree that allowing the user to set an arbitrary array lower bound was an enormous mistake. While we might not be able to ever completely remove that behavior, I find the idea of throwing an error to be highly enticing. Plus, as Marko said, this function is pretty useless for non-1-based arrays. I do agree that the name is probably too generic for this though. -- Jim C. Nasby, Data Architect j...@nasby.net 512.569.9461 (cell) http://jim.nasby.net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] array_length(anyarray)
On Jan9, 2014, at 23:26 , Jim Nasby j...@nasby.net wrote: On 1/9/14, 11:08 AM, Marko Tiikkaja wrote: On 1/9/14 5:44 PM, Florian Pflug wrote: On Jan9, 2014, at 14:57 , Dean Rasheed dean.a.rash...@gmail.com wrote: On 19 December 2013 08:05, Pavel Stehule pavel.steh...@gmail.com wrote: length should be irrelevant to fact so array starts from 1, 0 or anything else Yes, this should just return the number of elements, and 0 for an empty array. +1. Anything that complains about arrays whose lower bound isn't 1 really needs a *way* less generic name than array_length(). Problem is, if you're operating on an array which could have a lower bound that isn't 1, why would you look at the length in the first place? You can't access any elements by index, you'd need to look at array_lower(). You can't iterate over the array by index, you'd need to do array_lower() .. array_lower() + array_length(), which doesn't make sense. And then there's the myriad of stuff you can do with unnest() without actually having to look at the length. Same goes for multi-dimensional arrays: you have even less things you can do there with only a length. So if we give up these constraints, we also make this function completely useless. I'm generally opposed to creating code that doesn't support the full featureset of something (in this case, array_lower()1). But in this case I hope we can all agree that allowing the user to set an arbitrary array lower bound was an enormous mistake. No doubt. While we might not be able to ever completely remove that behavior, I find the idea of throwing an error to be highly enticing. Plus, as Marko said, this function is pretty useless for non-1-based arrays. That I doubt, but... I do agree that the name is probably too generic for this though. this one is actually my main complaint. The name needs to very clearly mark such a function as dealing only with a subset of all possible arrays. Otherwise we'll just add to the confusion, not avoid it. best regards, Florian Pflug -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] array_length(anyarray)
On Thu, Jan 9, 2014 at 11:08 AM, Marko Tiikkaja ma...@joh.to wrote: On 1/9/14 5:44 PM, Florian Pflug wrote: On Jan9, 2014, at 14:57 , Dean Rasheed dean.a.rash...@gmail.com wrote: On 19 December 2013 08:05, Pavel Stehule pavel.steh...@gmail.com wrote: length should be irrelevant to fact so array starts from 1, 0 or anything else Yes, this should just return the number of elements, and 0 for an empty array. +1. Anything that complains about arrays whose lower bound isn't 1 really needs a *way* less generic name than array_length(). Problem is, if you're operating on an array which could have a lower bound that isn't 1, why would you look at the length in the first place? You can't access any elements by index, you'd need to look at array_lower(). You can't iterate over the array by index, you'd need to do array_lower() .. array_lower() + array_length(), which doesn't make sense. And then there's the myriad of stuff you can do with unnest() without actually having to look at the length. Same goes for multi-dimensional arrays: you have even less things you can do there with only a length. I'm piling on: it's not clear at all to me why you've special cased this to lower_bound=1. First of all, there are other reasons to check length than iteration. If you want your code to blow up with non 1 based array, that should be checked in userland I think (perhaps with a constraint); the server API function should implement as many reasonable behaviors as possible. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] array_length(anyarray)
On 1/10/14, 1:20 AM, Merlin Moncure wrote: I'm piling on: it's not clear at all to me why you've special cased this to lower_bound=1. First of all, there are other reasons to check length than iteration. Can you point me to some examples? the server API function should implement as many reasonable behaviors as possible. That's exactly what I've done here. :-) Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] array_length(anyarray)
On Wed, Dec 18, 2013 at 09:27:54PM +0100, Marko Tiikkaja wrote: Hi, Attached is a patch to add support for array_length(anyarray), which only works for one-dimensional arrays, returns 0 for empty arrays and complains if the array's lower bound isn't 1. In other words, does the right thing when used with the arrays people use 99% of the time. +1 for adding this. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] array_length(anyarray)
2013/12/19 David Fetter da...@fetter.org On Wed, Dec 18, 2013 at 09:27:54PM +0100, Marko Tiikkaja wrote: Hi, Attached is a patch to add support for array_length(anyarray), which only works for one-dimensional arrays, returns 0 for empty arrays and complains if the array's lower bound isn't 1. In other words, does the right thing when used with the arrays people use 99% of the time. +1 for adding this. +1 length should be irrelevant to fact so array starts from 1, 0 or anything else Regards Pavel Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] array_length(anyarray)
Hi, Attached is a patch to add support for array_length(anyarray), which only works for one-dimensional arrays, returns 0 for empty arrays and complains if the array's lower bound isn't 1. In other words, does the right thing when used with the arrays people use 99% of the time. I'll add this to the next commit fest, but feel free to discuss it before that. 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,354 2 (1 row) /programlisting + + The single-argument overload of functionarray_length/function can be used + to get the length of a one-dimensional array: + programlisting + SELECT array_length(schedule) FROM sal_emp WHERE name = 'Carol'; + + array_length + -- + 2 + (1 row) + /programlisting /para /sect2 *** a/doc/src/sgml/func.sgml --- b/doc/src/sgml/func.sgml *** *** 11096,11101 SELECT NULLIF(value, '(none)') ... --- 11096,2 row entry literal + functionarray_length/function(typeanyarray/type) + /literal + /entry + entrytypeint/type/entry + entryreturns the length of the array (array must be one-dimensional)/entry + entryliteralarray_length(array[1,2,3])/literal/entry + entryliteral3/literal/entry +/row +row + entry + literal functionarray_lower/function(typeanyarray/type, typeint/type) /literal /entry *** a/src/backend/utils/adt/arrayfuncs.c --- b/src/backend/utils/adt/arrayfuncs.c *** *** 1740,1745 array_length(PG_FUNCTION_ARGS) --- 1740,1779 } /* + * array_length_single: + *Returns the length of a single-dimensional array. The array must be + *single-dimensional or empty and its lower bound must be 1. + */ + Datum + array_length_single(PG_FUNCTION_ARGS) + { + ArrayType *v = PG_GETARG_ARRAYTYPE_P(0); + int result; + int*lb; + int*dimv; + + /* empty array */ + if (ARR_NDIM(v) == 0) + PG_RETURN_INT32(0); + + if (ARR_NDIM(v) != 1) + ereport(ERROR, + (errcode(ERRCODE_ARRAY_SUBSCRIPT_ERROR), +errmsg(input array is not single-dimensional))); + + lb = ARR_LBOUND(v); + if (lb[0] != 1) + ereport(ERROR, + (errcode(ERRCODE_ARRAY_SUBSCRIPT_ERROR), +errmsg(lower bound of array is not 1))); + + dimv = ARR_DIMS(v); + result = dimv[0]; + PG_RETURN_INT32(result); + } + + + /* * 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 ( array_length PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 23 2277 _null_ _null_ _null_ _null_ array_length_single _null_ _null_ _null_ )); + DESCR(array length); DATA(insert OID = 378 ( array_appendPGNSP 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_length_single(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,1482 (1 row) + select array_length(NULL::int[]); + array_length + -- + + (1 row) + + select array_length(array[1,2,3]); + array_length + -- + 3 + (1 row) + + select array_length('{}'::int[]); +
Re: [HACKERS] array_length(anyarray)
On 12/18/2013 03:27 PM, Marko Tiikkaja wrote: Hi, Attached is a patch to add support for array_length(anyarray), which only works for one-dimensional arrays, returns 0 for empty arrays and complains if the array's lower bound isn't 1. In other words, does the right thing when used with the arrays people use 99% of the time. Why the heck would it complain if the lower bound isn't 1? cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] array_length(anyarray)
On 2013-12-18 22:13, Andrew Dunstan wrote: On 12/18/2013 03:27 PM, Marko Tiikkaja wrote: Attached is a patch to add support for array_length(anyarray), which only works for one-dimensional arrays, returns 0 for empty arrays and complains if the array's lower bound isn't 1. In other words, does the right thing when used with the arrays people use 99% of the time. Why the heck would it complain if the lower bound isn't 1? Because then you're free to assume that the first element is [1] and the last one is [array_length()]. Which is what 99% of the code using array_length(anyarray, int) does anyway. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] array_length(anyarray)
On 2013-12-18 22:19, I wrote: On 2013-12-18 22:13, Andrew Dunstan wrote: On 12/18/2013 03:27 PM, Marko Tiikkaja wrote: Attached is a patch to add support for array_length(anyarray), which only works for one-dimensional arrays, returns 0 for empty arrays and complains if the array's lower bound isn't 1. In other words, does the right thing when used with the arrays people use 99% of the time. Why the heck would it complain if the lower bound isn't 1? Because then you're free to assume that the first element is [1] and the last one is [array_length()]. Which is what 99% of the code using array_length(anyarray, int) does anyway. Just to clarify, I mean that array_lower(.., 1) must be 1. Whatever that's called. The lower bound of the only dimension of the array? Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] array_length(anyarray)
On 12/18/2013 04:19 PM, Marko Tiikkaja wrote: On 2013-12-18 22:13, Andrew Dunstan wrote: On 12/18/2013 03:27 PM, Marko Tiikkaja wrote: Attached is a patch to add support for array_length(anyarray), which only works for one-dimensional arrays, returns 0 for empty arrays and complains if the array's lower bound isn't 1. In other words, does the right thing when used with the arrays people use 99% of the time. Why the heck would it complain if the lower bound isn't 1? Because then you're free to assume that the first element is [1] and the last one is [array_length()]. Which is what 99% of the code using array_length(anyarray, int) does anyway. You're not really free to assume it - you'll need an exception handler for the other-than-1 case, or your code might blow up. This seems to be codifying a bad pattern, which should be using array_lower() and array_upper() instead. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] array_length(anyarray)
On 2013-12-18 22:32, Andrew Dunstan wrote: You're not really free to assume it - you'll need an exception handler for the other-than-1 case, or your code might blow up. This seems to be codifying a bad pattern, which should be using array_lower() and array_upper() instead. That's the entire point -- I *want* my code to blow up. If someone passes a multi-dimensional array to a function that assumes its input is one-dimensional and its indexes start from 1, I want it to be obvious that the caller did something wrong. Now I either copy-paste lines and lines of codes to always test for the weird cases or my code breaks in subtle ways. This is no different from an Assert() somewhere -- if the caller breaks the documented interface, it's his problem, not mine. And I don't want to waste my time coding around the fact that this simple thing is so hard to do in PG. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] array_length(anyarray)
Marko Tiikkaja-4 wrote On 2013-12-18 22:32, Andrew Dunstan wrote: You're not really free to assume it - you'll need an exception handler for the other-than-1 case, or your code might blow up. This seems to be codifying a bad pattern, which should be using array_lower() and array_upper() instead. That's the entire point -- I *want* my code to blow up. If someone passes a multi-dimensional array to a function that assumes its input is one-dimensional and its indexes start from 1, I want it to be obvious that the caller did something wrong. Now I either copy-paste lines and lines of codes to always test for the weird cases or my code breaks in subtle ways. This is no different from an Assert() somewhere -- if the caller breaks the documented interface, it's his problem, not mine. And I don't want to waste my time coding around the fact that this simple thing is so hard to do in PG. 1) Why cannot we just make the second argument of the current function optional and default to 1? 2) How about providing a function that returns the 1-dim/lower=1 input array or raise/exception if the input array does not conform? not tested/psuedo-code CREATE FUNCTION array_normal(arr anyarray) RETURNS anyarray $$ begin if (empty(arr)) return arr; if (ndim(arr) 1) raise exception; if (array_lower() 1) raise exception return arr; end; $$ I can also see wanting 1-dimensional enforced without having to require the lower-bound to be 1 so maybe a separate function for that. Usage: SELECT array_length(array_normal(input_array)) I could see this being especially useful for a domain and/or column constraint definition and also allowing for a textbook case of separation of concerns. I am torn, but mostly opposed, to making an array_length(anyarray) function with these limitations enforced - especially if other similar functions are not created at the same time. I fully agree that array_length(anyarray) should be a valid call without requiring the user to specify , 1 by rote. Tangential Question: Is there any way to define a non-1-based array without using array-literal syntax but by using ARRAY[1,2,3] syntax? David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/array-length-anyarray-tp5783950p5783972.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] array_length(anyarray)
On 12/19/13, 12:01 AM, David Johnston wrote: Marko Tiikkaja-4 wrote On 2013-12-18 22:32, Andrew Dunstan wrote: You're not really free to assume it - you'll need an exception handler for the other-than-1 case, or your code might blow up. This seems to be codifying a bad pattern, which should be using array_lower() and array_upper() instead. That's the entire point -- I *want* my code to blow up. If someone passes a multi-dimensional array to a function that assumes its input is one-dimensional and its indexes start from 1, I want it to be obvious that the caller did something wrong. Now I either copy-paste lines and lines of codes to always test for the weird cases or my code breaks in subtle ways. This is no different from an Assert() somewhere -- if the caller breaks the documented interface, it's his problem, not mine. And I don't want to waste my time coding around the fact that this simple thing is so hard to do in PG. 1) Why cannot we just make the second argument of the current function optional and default to 1? That still does the wrong thing for the empty array, multidimensional arrays and arrays that don't start from index 1. 2) How about providing a function that returns the 1-dim/lower=1 input array or raise/exception if the input array does not conform? not tested/psuedo-code CREATE FUNCTION array_normal(arr anyarray) RETURNS anyarray $$ begin if (empty(arr)) return arr; if (ndim(arr) 1) raise exception; if (array_lower() 1) raise exception return arr; end; $$ With this, I would still have to do COALESCE(array_length(array_normal($1), 1), 0). That's pretty stupid for the most common use case of arrays, don't you think? I can also see wanting 1-dimensional enforced without having to require the lower-bound to be 1 so maybe a separate function for that. I really don't see the point. How often have you ever created a function that doesn't have a lower bound of 1 on purpose? What good did it serve you? Usage: SELECT array_length(array_normal(input_array)) I could see this being especially useful for a domain and/or column constraint definition and also allowing for a textbook case of separation of concerns. What would array_length() in this case be? With what you suggested above, you would still get NULL for an empty array. I am torn, but mostly opposed, to making an array_length(anyarray) function with these limitations enforced - especially if other similar functions are not created at the same time. I fully agree that array_length(anyarray) should be a valid call without requiring the user to specify , 1 by rote. I'm specifically asking for something that is different from array_length(anyarray, int), because I personally think it's too full of caveats. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers