returning an array as a list of single-column rows... (different approach)

[<pgsql(at)yukonho(dot)de>]

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