Thread: returning an array as a list fo single-column rows?

Hi,

is there a way to return a Pg array as a list of single-column row 
values?

I am trying to circumvent DBI's lack of support for native database 
arrays and return the list of values from an ENUM as a perl array.

Thanks,

Hello

try

create or replace function unpack(anyarray)
returns setof anyelement as $$ select $1[i]    from generate_series(array_lower($1,1), array_upper($1,1)) g(i);
$$ language sql;

postgres=# select * from unpack(array[1,2,3,4]);unpack
--------     1     2     3     4
(4 rows)

Regards
Pavel Stehule

On 23/12/2007, Louis-David Mitterrand
<vindex+lists-pgsql-sql@apartia.org> wrote:
> Hi,
>
> is there a way to return a Pg array as a list of single-column row
> values?
>
> I am trying to circumvent DBI's lack of support for native database
> arrays and return the list of values from an ENUM as a perl array.
>
> Thanks,
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
>                 http://www.postgresql.org/about/donate
>

On Sun, Dec 23, 2007 at 10:19:26PM +0100, Pavel Stehule wrote:
> Hello
> 
> try
> 
> create or replace function unpack(anyarray)
> returns setof anyelement as $$
>   select $1[i]
>      from generate_series(array_lower($1,1), array_upper($1,1)) g(i);
> $$ language sql;
> 
> postgres=# select * from unpack(array[1,2,3,4]);
>  unpack
> --------
>       1
>       2
>       3
>       4
> (4 rows)

Beautiful. Thank you.

On 23/12/2007, Louis-David Mitterrand
<vindex+lists-pgsql-sql@apartia.org> wrote:
> Hi,
>
> is there a way to return a Pg array as a list of single-column row
> values?
>
> I am trying to circumvent DBI's lack of support for native database
> arrays and return the list of values from an ENUM as a perl array.
>
> Thanks,
>

you can solve this problem with conversion to string with const separator

Like:

postgres=# select array_to_string(array[1,2,3,4],'|');array_to_string
-----------------1|2|3|4
(1 row)

[pavel@localhost ~]$ perl
@a = split(/\|/, "1|2|3");
print $a[1];

Regards
Pavel

On Sun, Dec 23, 2007 at 10:27:09PM +0100, Pavel Stehule wrote:
> On 23/12/2007, Louis-David Mitterrand
> <vindex+lists-pgsql-sql@apartia.org> wrote:
> > Hi,
> >
> > is there a way to return a Pg array as a list of single-column row
> > values?
> >
> > I am trying to circumvent DBI's lack of support for native database
> > arrays and return the list of values from an ENUM as a perl array.
> >
> > Thanks,
> >
> 
> you can solve this problem with conversion to string with const separator
> 
> Like:
> 
> postgres=# select array_to_string(array[1,2,3,4],'|');
>  array_to_string
> -----------------
>  1|2|3|4
> (1 row)
> 
> [pavel@localhost ~]$ perl
> @a = split(/\|/, "1|2|3");
> print $a[1];

Yes I thought about it, but would rather have Pg do the array splitting. 
For instance if the separator occurs in an array element there is no 
built-in escaping:

% select array_to_string(array['ee','dd','rr','f|f'],'|');array_to_string ----------------- ee|dd|rr|f|f

... and then perl would have it all wrong.

>
> Yes I thought about it, but would rather have Pg do the array splitting.
> For instance if the separator occurs in an array element there is no
> built-in escaping:
>
> % select array_to_string(array['ee','dd','rr','f|f'],'|');
>  array_to_string
>  -----------------
>   ee|dd|rr|f|f

if you have not some special char, then unpack is one possible solution

theoretically you can use text output

postgres=# select array['aa','aaa,j']::text;     array
-----------------{aa,"aaa,j"}
(1 row)

but nothing nice parse it :(

An: pgsql-sql@postgresql.org
Betreff: Re: [SQL] returning an array as a list fo single-column rows?

The following will return the elements of an array each in its
Own row.  Using both array_lower() and array_upper() the number of array
Elements and their internal index may vary from record to record. Or may
even be absent.
Within the record the array nstat[],nwert[],nwho[] must correspond. 
Joining the table with
generate_series(array_lower(nWert,1),array_upper(nWert,1)) as indx

returns the contained array elements.


Considering the following table with array.....

Create table werte 
(id : integer, ...... ......nstat  : character(1)[],nwert  : double precision[],nwho   : character varying(9)[]
);


select
w.id,ii.indx,
w.nStat[ii.indx],w.nWert[ii.indx],w.nWho[ii.indx]
from werte w
join
(
select id,generate_series(array_lower(nWert,1),array_upper(nWert,1)) as indxfrom werte
) ii on ii.id=w.id 
;


Let me know what you think about this approach?


My best regards,


Stefan Becker