Re: Should array_length() Return NULL

On 17 March 2013 05:19, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Brendan Jurd <direvus(at)gmail(dot)com> writes:
>> On 16 March 2013 09:07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> The thing is that that syntax creates an array of zero dimensions,
>>> not one that has 1 dimension and zero elements.
>
>> I'm going to ask the question that immediately comes to mind: Is there
>> anything good at all about being able to define a zero-dimensional
>> array?
>
> Perhaps not. I think for most uses, a 1-D zero-length array would be
> just as good. I guess what I'd want to know is whether we also need
> to support higher-dimensional zero-size arrays, and if so, what does
> the I/O syntax for those look like?

If I'm reading right, in our current implementation of array
dimensionality, there can be no such thing as a higher-dimensional
zero-length array anyhow. Postgres doesn't care about how many
dimensions you define for an array, it uses the "quacks like a duck"
test for number of dimensions. For example:

postgres=# SELECT ARRAY[1]::int[][], array_dims(ARRAY[1]::int[][]);
array | array_dims
-------+------------
{1} | [1:1]

postgres=# SELECT ARRAY[ARRAY[1]]::int[];
array
-------
{{1}}

postgres=# SELECT ARRAY[ARRAY[1]]::int[][];
array
-------
{{1}}

Some array functions just plain don't work with multiple dimensions:

postgres=# SELECT array_append(ARRAY[ARRAY[1]]::int[][], ARRAY[2]);
ERROR: function array_append(integer[], integer[]) does not exist

So, to answer your question, no, I don't think we would need to
support it, at least not unless/until there is a major change and
number of dimensions becomes more meaningful. You can start out with
a zero-length array (which has one dimension) and then add nested
arrays to it if you want to -- it will then ipso facto have multiple
dimensions.

> Another fly in the ointment is that if we do redefine '{}' as meaning
> something other than a zero-D array, how will we handle existing
> database entries that are zero-D arrays?
>

I would go with zero-length 1-D. It's almost certainly what the
author intended.

I'd be more worried about the possibility of, say, PL/pg functions in
the field that rely on our existing bizarre behaviours to test for an
"empty" array, like IF array_length(A) IS NULL, or IF array_dims(A) IS
NULL. I'm pretty sure I have some such tests in my applications, and
I still think breaking them is a reasonable price to pay for greater
sanity.

Cheers,
BJ