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/