[database-devel] Arrays and postgresql-simple

[Leon Smith]

Ok,  Bas van Dijk has done some preliminary work in adding support for
PostgreSQL's array types to postgresql-simple,  and I have some questions
about arrays and types that I'm hoping Jeff Davis can answer.   Here's a
link, and a simple experiment in psql:

https://github.com/basvandijk/postgresql-simple/commit/eb04ca39c5c22e3f4d083ba4986ab9e8339ed7d1


=> create table strings (x text);
=> insert into strings values ('foo','bar','baz','hello world','goodbye
cruel world');
=> select array_agg(x) from strings group by x like ('% %');
               array_agg
---------------------------------------
 {foo,bar,baz}
 {"hello world","goodbye cruel world"}
(2 rows)


So far so good,  but what if we try creating an array of arrays?


=> select array_agg(*) from (select array_agg(x) from strings group by x
like ('% %')) q;
ERROR:  function array_agg() does not exist
LINE 1: select array_agg(*) from (select array_agg(x) from strings g...
               ^
HINT:  No function matches the given name and argument types. You might
need to add explicit type casts.

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...

The reason I ask is that Bas van Dijk has done some work on adding support
for arrays to postgresql-simple.   And he's modified the type cache from
TypeOID ->  IO  TypeName to become a TypeOID -> IO TypeInfo,  where the
TypeInfo type is defined as follows:

data NamedOid = NamedOid { typoid  :: !PQ.Oid
                         , typname :: !ByteString
                         } deriving Show

data TypeInfo = TypeInfo { typ     :: !NamedOid
                         , typelem :: !(Maybe NamedOid)
                         } deriving Show

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

The real issue here is one dealing with purity and effects:   In some
sense,  Bas's first attempt carries the same information,  but you might
need to do some IO in order to retrieve it. (Though this assumes that one
can query the type cache directly,  which is something I should probably
add anyway...)  But the fromField method,  which is is the most likely
consumer of this information,  isn't allowed to do IO.   So I think we
really want to change this type.

Also,  this type makes it more natural to add support for the new range
types available in PostgreSQL 9.2.    (Which Jeff is largely responsible
for,  by the way...)    The extended TypeInfo might look like this:

data TypeInfo = Plain { typ     :: !NamedOid }
              | Array { typ     :: !NamedOid
                      , typelem :: !TypeInfo }
              | Range { typ     :: !NamedOid
                      , typelem :: !TypeInfo }
                deriving Show

So,  I think that's enough issues to talk about for the time being.

Best,
Leon
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.haskell.org/pipermail/database-devel/attachments/20120730/d8ac1cd5/attachment.htm>