Lists: | pgsql-sql |
---|
From: | Louis-David Mitterrand <vindex+lists-pgsql-sql(at)apartia(dot)org> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | returning an array as a list fo single-column rows? |
Date: | 2007-12-23 20:55:57 |
Message-ID: | 20071223205557.GA14403@apartia.fr |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
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,
From: | "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: returning an array as a list fo single-column rows? |
Date: | 2007-12-23 21:19:26 |
Message-ID: | 162867790712231319i35fb31el8d4c367bc313979b@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
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(at)apartia(dot)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
>
From: | Louis-David Mitterrand <vindex+lists-pgsql-sql(at)apartia(dot)org> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: returning an array as a list fo single-column rows? |
Date: | 2007-12-23 21:22:05 |
Message-ID: | 20071223212205.GA15367@apartia.fr |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
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.
From: | "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: returning an array as a list fo single-column rows? |
Date: | 2007-12-23 21:27:09 |
Message-ID: | 162867790712231327g774b8c5ld488113a3a8252e9@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
On 23/12/2007, Louis-David Mitterrand
<vindex+lists-pgsql-sql(at)apartia(dot)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(at)localhost ~]$ perl
@a = split(/\|/, "1|2|3");
print $a[1];
Regards
Pavel
From: | Louis-David Mitterrand <vindex+lists-pgsql-sql(at)apartia(dot)org> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: returning an array as a list fo single-column rows? |
Date: | 2007-12-23 21:33:45 |
Message-ID: | 20071223213345.GA15623@apartia.fr |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
On Sun, Dec 23, 2007 at 10:27:09PM +0100, Pavel Stehule wrote:
> On 23/12/2007, Louis-David Mitterrand
> <vindex+lists-pgsql-sql(at)apartia(dot)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(at)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.
From: | "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: returning an array as a list fo single-column rows? |
Date: | 2007-12-23 21:45:01 |
Message-ID: | 162867790712231345w7c01493fv90517ba2dc9a90ff@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
>
> 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 :(
From: | <pgsql(at)yukonho(dot)de> |
---|---|
To: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | returning an array as a list of single-column rows... (different approach) |
Date: | 2007-12-23 23:02:45 |
Message-ID: | 001101c845b7$ee686590$cb3930b0$@de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
An: pgsql-sql(at)postgresql(dot)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 indx
from werte
) ii on ii.id=w.id
;
Let me know what you think about this approach?
My best regards,
Stefan Becker