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

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

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

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

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

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

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

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 <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@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

2013/12/19 David Fetter <david@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

On 19 December 2013 08:05, Pavel Stehule <pavel.stehule@gmail.com> wrote:

2013/12/19 David Fetter <david@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

On Jan9, 2014, at 14:57 , Dean Rasheed <dean.a.rasheed@gmail.com> wrote:

On 19 December 2013 08:05, Pavel Stehule <pavel.stehule@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

On 1/9/14 5:44 PM, Florian Pflug wrote:

On Jan9, 2014, at 14:57 , Dean Rasheed <dean.a.rasheed@gmail.com> wrote:

On 19 December 2013 08:05, Pavel Stehule <pavel.stehule@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

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.rasheed@gmail.com> wrote:

On 19 December 2013 08:05, Pavel Stehule <pavel.stehule@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 jim@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

On Jan9, 2014, at 23:26 , Jim Nasby <jim@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.rasheed@gmail.com> wrote:

On 19 December 2013 08:05, Pavel Stehule <pavel.stehule@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

On Thu, Jan 9, 2014 at 11:08 AM, Marko Tiikkaja <marko@joh.to> wrote:

On 1/9/14 5:44 PM, Florian Pflug wrote:

On Jan9, 2014, at 14:57 , Dean Rasheed <dean.a.rasheed@gmail.com> wrote:

On 19 December 2013 08:05, Pavel Stehule <pavel.stehule@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

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

On 10 January 2014 00:36, Marko Tiikkaja <marko@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

On 1/10/14, 9:04 AM, Dean Rasheed wrote:

On 10 January 2014 00:36, Marko Tiikkaja <marko@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

On Fri, Jan 10, 2014 at 2:04 AM, Dean Rasheed <dean.a.rasheed@gmail.com> wrote:

On 10 January 2014 00:36, Marko Tiikkaja <marko@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 <marko@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