On Wed, Jun 12, 2013 at 5:05 AM, Brendan Jurd <direvus@gmail.com> wrote:
> On 12 June 2013 18:22, Dean Rasheed <dean.a.rasheed@gmail.com> wrote:
>> +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.
>
> It's true that our multidims aren't nested, but they are the nearest
> thing we have. If we want to keep the door open for future attempts
> to nudge multidim arrays into closer approximation of nested arrays,
> it would be better to have the nested interpretation of CARDINALITY.
I think there's just about zero chance of something like that ever
happening. The problem is that our type system just can't support it.A function or operator that takes an array needs
todeclare whether
it's going to return an array or whether it's going to return the base
type. It can't decide to return one or the other at run-time
depending on the dimensionality of the array.
For this to really work, we'd need the number of dimensions to be
baked into the array type. The obvious implementation would be to
have N array types per base type rather than 1, each with a different
number of dimensions. Then a subscripting function which took a
1-dimensional array could return anyelement, and the similarly named
function which took a 2-dimensional array could return a 1-dimensional
array.
I believe the reason it wasn't done this way initially was because of
pg_type bloat; having 6 extra type definitions for every type we
support is unappealing. We could force them to be explicitly declared
as we do for range types. Or we could rewrite a whole lotta code to
understand a "type" as something more complex than "an OID from
pg_type", so that we don't need pre-defined entries in pg_type for
array types in the first place.
But none of these things are nudges. Making any real improvement in
this area is going to take major surgery, not a nudge.
> Multidim arrays are why we can't have nice things.
Yeah, I think that was not our best moment. :-(
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company