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

On Wed, Sep 02, 2009 at 03:47:53PM +0200, Massa, Harald Armin wrote:
> ibox=# CREATE TYPE intarr AS (arr int[]);
> CREATE TYPE
> ibox=#  SELECT array(
>       SELECT x::intarr FROM (
>       SELECT array[2,3]
>       UNION ALL
>       SELECT array[3,4]) x(a));
>            ?column?
> -------------------------------
>  {"(\"{2,3}\")","(\"{3,4}\")"}
> (1 Zeile)
>
> .... the result seems to be an array with two strings containing escaped
> string-represenations of arrays :)

I think that's what you want though--PG just formats the literal the
only way it knows how.  You can use the normal array indexing operators
to get the elements out that you want.  For example:

  SELECT x.arr[1].arr[1]
  FROM (SELECT e'{"(\\"{2,3}\\")","(\\"{3,4}\\")"}'::intarr[]) x(arr);

Or from your original query:

  SELECT x.arr[1].arr[1]
  FROM (
    SELECT array(
      SELECT x::intarr FROM (
         SELECT array[2,3]
         UNION ALL
         SELECT array[3,4]) x(a))) x(arr);

If you really do care how the literals are formatted, then you're going
to have to come up with your own data type and associated input and
output functions.

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