[database-devel] Arrays and postgresql-simple

[Leon Smith]

Well, this is the kind of misinformation that needs to be cleared up.
 Thanks Joey. =)

Some of this is understandable,  some of it is unfortunate.  I'd go so far
as to say that the array_agg issue seems to be a design bug.  I mean,
PostgreSQL is relatively nice for SQL, but it is still SQL... (sigh)

So can the types that can be array elements be extended in any way,  or are
these types set in stone? (without modifying the PostgreSQL source itself,
of course...)    And if the array elements are set,  does 9.2 support
arrays of range types?

The TypeInfo declaration I suggested might still be better, even if the set
of types that PostgreSQL supports is rather more limited than what it can
express.   But I may well be wrong.

And regarding the array parsing issue,  to be honest I wasn't going to
review that code too closely and trust whomever to get it approximately
right,  and then worry about fixing it if somebody found some issues.   But
if you've dealt with this issue already,   then I think you should post
your code and then hopefully we can figure out how to improve one using
ideas from the other.


On Mon, Jul 30, 2012 at 8:06 PM, Joey Adams <joeyadams3.14159 at gmail.com>wrote:

> On Mon, Jul 30, 2012 at 5:00 PM, Leon Smith <leon.p.smith at gmail.com>
> 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, PostgreSQL does not truly support the notion of "arrays of
> arrays".  PostgreSQL arrays are actually N-dimensional matrices, where
> N is a number from 1 to 6. For example, '{{1,2},{3,4}}' is a valid
> array, but '{{1,2},{3}}' is not (inconsistent dimensions).
>
> Some other weird things about PostgreSQL arrays:
>
>  * None of an array's dimensions may be zero. For example, '{{},{}}'
> is invalid. However, an empty, dimensionless array is allowed: '{}'.
>
>  * Many aggregate functions, such as array_agg, return null instead of
> an empty array when given an empty set.  Care must be taken when using
> array_agg to construct an array.
>
>  * Not every type uses ',' as the delimiter character (but almost all
> of them do). For example, the box type uses ';' instead, as it uses
> ',' to delimit coordinates.
>
>    The typdelim column in pg_type [1] indicates what delimiter a given
> type uses.
>
>  * PostgreSQL arrays can have explicit dimensions:
>
>         > SELECT '[5]={1,2,3,4,5}'::int[];
>             int4
>         -------------
>          {1,2,3,4,5}
>
>         > SELECT '[3:5]={3,4,5}'::int[];
>              int4
>         ---------------
>          [3:5]={3,4,5}
>         > SELECT '[2][3]={{1,2,3},{4,5,6}}'::int[];
>                int4
>         -------------------
>          {{1,2,3},{4,5,6}}
>
> As you can see, PostgreSQL arrays are perilous.  They're useful
> nonetheless.  I would benefit from postgresql-simple having basic
> array support.  It would be one less obstacle to switching my
> application to use postgresql-simple instead of the crummy DB code I
> wrote.
>
> In that crummy code, I have a function for reading a PostgreSQL array.
>  It supports explicit dimensions, NULL, and unescaping (array items
> can be quoted with double quotes, and may contain backslash escapes).
> However, it only supports single-dimensional arrays, and I have not
> written a corresponding function to generate a PostgreSQL array.
>
> Want me to clean up my array parsing code and post it?
>
> Thanks,
> -Joey
>
>  [1]: http://www.postgresql.org/docs/current/static/catalog-pg-type.html
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.haskell.org/pipermail/database-devel/attachments/20120730/93bcaa20/attachment.htm>