Re: Closing some 8.4 open items

On Wednesday 08 April 2009 21:56:38 Tom Lane wrote:
> > For my part, I'd like to know what things other than arrays
> > <collection_expression> in the standard applies to. I think the most
> > sensible course is to make cardinality(array[]) behave consistently with
> > cardinality(other_stuff) when we get around to implementing it.
>
> There is no equivalent of multi-dimensional arrays in other kinds of
> collections, so I'm not seeing that there is any good guide there.

Here is my thinking, and considering that that would basically involve a
forward-looking design decision right now, I would support dropping the
cardinality() function from 8.4 (if people agree that this is in fact the
design decision to make).

Collection types in SQL are arrays and multisets. Multisets are essentially
arrays without ordering. Many people already use arrays like that, and I
would find it interesting to support real multisets in the future.

Currently, we don't support collections of collections, specifically arrays of
arrays. We only have multidimensional arrays. Multidimensional arrays in
PostgreSQL and arrays of arrays in SQL are actually pretty close in the
interface they present, except that the subscript order is reversed. If you
ignore that, the current cardinality() function gives you pretty much
conforming behavior on "nested arrays", at least for the first level.

The question now is, if we want to move toward supporting multisets and
arbitrary nested collections in the future, do we

1. Transform our view of a multidimensional array into nested arrays, and then
extend that to allow multisets. (The implementation could stay quite the
same; just mark some dimensions as "this is a multiset".) And then perhaps
address the subscript ordering issue by some hitherto unknown plan.

- or -

2. Extend the system so you can have nested multidimensional arrays (e.g., a
4x4 array containing 3x3 arrays), and then extend that to also allow nesting
with a separate multiset structure (possibly also multidimensional). I think
this would probably make a mess out of the subscripting.

- or -

3. SQL DIE DIE DIE!!!

If you think (1) then the current implementation of cardinality() is correct,
if you think (2) then Tom's proposed change is correct, if you think (3) the
function should be removed.