From: pgsql@yukonho.de
To: pgsql-sql@postgresql.org
Subject: returning an array as a list of single-column rows... (different approach)
Date: Mon, 24 Dec 2007 00:02:45 +0100
Message-ID: <001101c845b7$ee686590$cb3930b0$@de> (raw)
In-Reply-To: <162867790712231345w7c01493fv90517ba2dc9a90ff@mail.gmail.com>
References: <20071223205557.GA14403@apartia.fr>
<162867790712231327g774b8c5ld488113a3a8252e9@mail.gmail.com>
<20071223213345.GA15623@apartia.fr>
<162867790712231345w7c01493fv90517ba2dc9a90ff@mail.gmail.com>
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 indx
from werte
) ii on ii.id=w.id
;
Let me know what you think about this approach?
My best regards,
Stefan Becker
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: pgsql@yukonho.de
Subject: Re: returning an array as a list of single-column rows... (different approach)
In-Reply-To: <001101c845b7$ee686590$cb3930b0$@de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox