Well, this is the kind of misinformation that needs to be cleared up. Thanks Joey. =)<div><br></div><div>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)</div>
<div><br></div><div>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?</div>
<div><br></div><div>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.</div><div><br></div>
<div>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. </div>
<div><br></div><div><br><div class="gmail_quote">On Mon, Jul 30, 2012 at 8:06 PM, Joey Adams <span dir="ltr"><<a href="mailto:joeyadams3.14159@gmail.com" target="_blank">joeyadams3.14159@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div class="im">On Mon, Jul 30, 2012 at 5:00 PM, Leon Smith <<a href="mailto:leon.p.smith@gmail.com">leon.p.smith@gmail.com</a>> wrote:<br>
> Now, presumably, postgresql does actually support arrays of arrays, but<br>
> I'm guessing that you need to create the array of array of string type<br>
> before you can actually run this query...<br>
<br>
</div>Actually, PostgreSQL does not truly support the notion of "arrays of<br>
arrays". PostgreSQL arrays are actually N-dimensional matrices, where<br>
N is a number from 1 to 6. For example, '{{1,2},{3,4}}' is a valid<br>
array, but '{{1,2},{3}}' is not (inconsistent dimensions).<br>
<br>
Some other weird things about PostgreSQL arrays:<br>
<br>
* None of an array's dimensions may be zero. For example, '{{},{}}'<br>
is invalid. However, an empty, dimensionless array is allowed: '{}'.<br>
<br>
* Many aggregate functions, such as array_agg, return null instead of<br>
an empty array when given an empty set. Care must be taken when using<br>
array_agg to construct an array.<br>
<br>
* Not every type uses ',' as the delimiter character (but almost all<br>
of them do). For example, the box type uses ';' instead, as it uses<br>
',' to delimit coordinates.<br>
<br>
The typdelim column in pg_type [1] indicates what delimiter a given<br>
type uses.<br>
<br>
* PostgreSQL arrays can have explicit dimensions:<br>
<br>
> SELECT '[5]={1,2,3,4,5}'::int[];<br>
int4<br>
-------------<br>
{1,2,3,4,5}<br>
<br>
> SELECT '[3:5]={3,4,5}'::int[];<br>
int4<br>
---------------<br>
[3:5]={3,4,5}<br>
> SELECT '[2][3]={{1,2,3},{4,5,6}}'::int[];<br>
int4<br>
-------------------<br>
{{1,2,3},{4,5,6}}<br>
<br>
As you can see, PostgreSQL arrays are perilous. They're useful<br>
nonetheless. I would benefit from postgresql-simple having basic<br>
array support. It would be one less obstacle to switching my<br>
application to use postgresql-simple instead of the crummy DB code I<br>
wrote.<br>
<br>
In that crummy code, I have a function for reading a PostgreSQL array.<br>
It supports explicit dimensions, NULL, and unescaping (array items<br>
can be quoted with double quotes, and may contain backslash escapes).<br>
However, it only supports single-dimensional arrays, and I have not<br>
written a corresponding function to generate a PostgreSQL array.<br>
<br>
Want me to clean up my array parsing code and post it?<br>
<br>
Thanks,<br>
-Joey<br>
<br>
[1]: <a href="http://www.postgresql.org/docs/current/static/catalog-pg-type.html" target="_blank">http://www.postgresql.org/docs/current/static/catalog-pg-type.html</a><br>
</blockquote></div><br></div>