Re: creating array of integer[] out of query - how? - Mailing list pgsql-general

On Wed, Sep 02, 2009 at 11:50:38AM +0200, Massa, Harald Armin wrote:
>   select array(
> >  select x from (
> >  select array[2,3] as a
> >  union
> >  select array[3,4] as a ) x);
> >
> > ERROR:  could not find array type for datatype record
>
> ... I remember being there before :( arrays of rows are also not available.

Doh, sorry I forgot that that's an 8.4 only.  Before that you must
create your own composite type.

> To all: is there a deeper reason why there is no array type for datatype
> record available?

Not enough demand :)

>   [1] http://www.postgresql.org/docs/current/static/sql-createtype.html
> >
> > Thanks for the hint with CREATE TYPE, especially the lines
>
> """
> Whenever a user-defined type is created, PostgreSQL automatically creates an
> associated array type,
> """
> fills me with joy. ;)

Try:

  CREATE TYPE intarr AS (arr int[]);
  SELECT array(
    SELECT x::intarr FROM (
      SELECT array[2,3]
      UNION ALL
      SELECT array[3,4]) x(a));

and it should do the right thing in 8.3.

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