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

Hello,

if I use this statement:

postgres=# select array[[2,3],[3,4]];
     array
---------------
 {{2,3},{3,4}}

-> the result looks for me as an array of integer-arrays

now I try:

select array(
select a from
(
select array[2,3] as a
union

select array[3,4] as a
) x);

and the result is:

FEHLER:  could not find array type for datatype integer[]

Is there a bug in my thinking that array[[2,3],[3,4]] really constitutes an array of integer[],

or is there any cast missing,

or is a bug anywhere else?

Best wishes,

Harald

--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
no fx, no carrier pigeon
-
LASIK good, steroids bad?

On Wed, Sep 02, 2009 at 10:34:31AM +0200, Massa, Harald Armin wrote:
> Is there a bug in my thinking that array[[2,3],[3,4]] really constitutes an
> array of integer[],

no. array[[2,3],[3,4]] is 2 dimensional array of integers. not array of
arrays of integers.

Best regards,

depesz

--
Linkedin: http://www.linkedin.com/in/depesz  /  blog: http://www.depesz.com/
jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007

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

Sam,

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. ;)

Thanks to depesz & you,

Harald

--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
no fx, no carrier pigeon
-
LASIK good, steroids bad?

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/

Sam,

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

not exactly :)
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 guess I will try to solve my challenge without arrays of arrays or records :)

Thanks for trying,

Harald

--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
no fx, no carrier pigeon
-
LASIK good, steroids bad?

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/

On Wed, Sep 2, 2009 at 9:47 AM, Massa, Harald Armin<chef@ghum.de> wrote:
> Sam,
>
>> To all: is there a deeper reason why there is no array type for datatype
>> record available?
>>
>> Not enough demand :)
>
> seams reasonable :)
>
>>
>> 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.
>
> not exactly :)
> 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 :)


nope...it's an array of composite types, each type with one field, and
array of two ints. this is waht you wanted? what are you trying to do
exactly?

merlin

>nope...it's an array of composite types, each type with one field, and
>array of two ints. this is waht you wanted? what are you trying to do
>exactly?

yeah, that is quite what I want, just was "surprised" by the way psql displayed the result. What I am doing:

I have a table:

key1  key2 key3 infofield

and need/want to transform it into a new dataset with:

key1, array([key3, infofield])
     where key2=:externvalue

using that new type from sam I can do that now.

Many thanks!

Harald


--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
no fx, no carrier pigeon
-
LASIK good, steroids bad?