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 |
Thread: | |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Dani Castaños | 2007-12-24 12:49:03 | Different Query plans filtering between dates |
Previous Message | Pavel Stehule | 2007-12-23 21:45:01 | Re: returning an array as a list fo single-column rows? |