Skip site navigation (1) Skip section navigation (2)

Re: array variables

From: "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: "Marcin Krawczyk" <jankes(dot)mk(at)gmail(dot)com>, pgsql-sql(at)postgresql(dot)org
Subject: Re: array variables
Date: 2008-11-13 13:16:04
Message-ID: 162867790811130516s6cf0d92cvdc9cb8f84488babe@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-sql
2008/11/13 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com> writes:
>> 2008/11/13 Marcin Krawczyk <jankes(dot)mk(at)gmail(dot)com>:
>>> Because if I try this :
>>> a := ARRAY(SELECT id, p FROM idx);
>>> I get
>>> ERROR: subquery must return only one column
>
>> you can't do it directly :( now.
>
> Sure you can, if you're using a version new enough to have arrays of
> composite types.
>
> regression=# create table t1 (f1 int, f2 text);
> CREATE TABLE
> regression=# insert into t1 values (1,'one');
> INSERT 0 1
> regression=# insert into t1 values (2,'two');
> INSERT 0 1
> regression=# select array(select row(t1.*)::t1 from t1);
>       ?column?
> -----------------------
>  {"(1,one)","(2,two)"}
> (1 row)
>
> Whether this is a good idea for a large table is a different question ;-)

I don't expect so user use devel version ;) - and result is array of
some composite type, not two dimensional array (but arrays of records
is nice feature too).

regards
Pavel

>
>                        regards, tom lane
>

In response to

Responses

pgsql-sql by date

Next:From: Tom LaneDate: 2008-11-13 13:42:21
Subject: Re: array variables
Previous:From: Tom LaneDate: 2008-11-13 13:14:16
Subject: Re: array variables

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group