Re: [database-devel] Arrays and postgresql-simple

18 Aug 2012

      On Mon, 2012-07-30 at 17:00 -0400, Leon Smith wrote:
...
Now,  presumably,  postgresql does actually support arrays of arrays,
but I'm guessing that you need to create the array of array of string
type before you can actually run this query...
Actually, array support in postgresql is a little strange,
unfortunately. There is no way to create an array type (technically
there is in some special cases, which I just learned now, but that
doesn't work for arrays of arrays anyway); array types are created
implicitly for every non-array type.

The following won't work:

  select array_agg(xs) from
     (select array_agg(x) as xs from foo group by g) s;

But arrays are multi-dimensional, so the following does work:

  select ARRAY[ARRAY[1,2],ARRAY[3,4]];

I'm not sure what parts of this craziness are part of the SQL spec, and
what parts are unique to postgresql.
...
I think this is a perfectly reasonable first attempt,  but I don't
think it's correct,  because I'm pretty sure that PostgreSQL does
actually support arrays of arrays if you know what you are doing.   So
I think that TypeInfo needs to look something more like
data TypeInfo = Plain { typ     :: !NamedOid }
              | Array { typ     :: !NamedOid
                      , typelem :: !TypeInfo } 
                deriving Show
I think this version is a little closer to how the types are represented
in the PG catalog, so I think this is the right direction to go, but
there are some alternatives along these same lines. For instance:
...
data TypeInfo = Plain { typ     :: !NamedOid }
              | Array { typ     :: !NamedOid
                      , typelem :: !TypeInfo } 
              | Range { typ     :: !NamedOid
                      , typelem :: !TypeInfo } 
                deriving Show
That allows a range of arrays or an array of ranges, which is good,
because postgres supports those things too. But it supports arrays of
arrays, which postgresql does not (at least not in the type system).

A more complex proposal might look something like:

data TypeInfo = Plain     { typ         :: !NamedOid
                          , typarray    :: !NamedOid }
              | Enum 
              | Composite { typattrs    :: ![TypeInfo] }
              | Domain    { typbase     :: !NamedOid }
              | Array     { typ         :: !NamedOid
                          , typelem     :: !TypeInfo } 
              | Range     { typ         :: !NamedOid
                          , typarray    :: !NamedOid
                          , rngsubtype  :: !TypeInfo } 
                deriving Show

That still allows arrays of arrays, which is slightly awkward, but I
don't see a better way.

Am I overcomplicating things?

Regards,
	Jeff Davis