On 12 June 2013 04:50, David E. Wheeler <david@justatheory.com> wrote:
> On Jun 11, 2013, at 3:09 PM, Brendan Jurd <direvus@gmail.com> wrote:
>
>> There have been attempts to add a cardinality function in the past, as
>> it is required by the SQL spec, but these attempts have stalled when
>> trying to decide how it should handle multidim arrays. Having it
>> return the length of the first dimension is the more spec-compatible
>> way to go, but some folks argued that it should work as
>> ArrayGetNItems, because we don't already have a function for that at
>> the SQL level. Therefore I propose we add cardinality() per the spec,
>> and another function to expose ArrayGetNItems.
>>
>> And that's about where we got to, when the whole discussion was put on
>> a time-out to make room for the beta.
>>
>> I am withdrawing the original zero-D patch in favour of the proposed
>> new functions. If you have an opinion about that, please do chime in.
>> Depending on how that goes I may post a patch implementing my new
>> proposal in the next few days.
>
> +1 to this proposal. Modulo function names, perhaps. I don’t much care what they're called, as long as the work as
youdescribe here.
>
+1 for having a function to return the total number of elements in an
array, because that's something that's currently missing from SQL.
However, I think that CARDINALITY() should be that function.
I'm not convinced that having CARDINALITY() return the length of the
first dimension is more spec-compatible, since our multi-dimensional
arrays aren't nested arrays, and it seems unlikely that they ever will
be. I'd argue that it's at least equally spec-compatible to have
CARDINALITY() return the total number of elements in the array, if you
think of a multi-dimensional array as a collection of elements
arranged in a regular pattern.
Also, the spec describes CARDINALITY() and UNNEST() using the same
language, and I think it's implicit in a couple of places that
CARDINALITY() should match the number of rows returned by UNNEST(),
which we've already implemented as fully unnesting every element.
We're about to add ORDINALITY to UNNEST(), and to me it would be very
odd to have the resulting maximum ordinality exceed the array's
cardinality.
Regards,
Dean
