Re: creating array of integer[] out of query - how?

On Wed, Sep 02, 2009 at 10:34:31AM +0200, Massa, Harald Armin wrote:
> postgres=# select array[[2,3],[3,4]];
> array
> ---------------
> {{2,3},{3,4}}
>
> -> the result looks for me as an array of integer-arrays

No, as depesz says it's not doing that. Depending on what you want out
you can get most of the way by having an array of ROWs that contain an
array of integers. You just need to change:

> select array(
> select a from (
> select array[2,3] as a
> union
> select array[3,4] as a ) x);

to return "x" instead of "a" in the inner select. Something like:

select array(
select x from (
select array[2,3] as a
union
select array[3,4] as a ) x);

getting the resulting tuples out again is a bit of a struggle and you
may be better off with using a custom type. Have a look at CREATE
TYPE[1] for this.

--
Sam http://samason.me.uk/

[1] http://www.postgresql.org/docs/current/static/sql-createtype.html