Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id A4A869FB57C for ; Wed, 6 Jun 2007 12:52:43 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 51184-02-3 for ; Wed, 6 Jun 2007 12:52:37 -0300 (ADT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by postgresql.org (Postfix) with ESMTP id F34DA9FB552 for ; Wed, 6 Jun 2007 12:52:29 -0300 (ADT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.13.8/8.13.8) with ESMTP id l56FqKVl089400 for ; Wed, 6 Jun 2007 12:52:20 -0300 (ADT) (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.13.8/8.13.8/Submit) id l56FmTfv088637 for pgsql-admin@postgresql.org; Wed, 6 Jun 2007 12:48:29 -0300 (ADT) (envelope-from news) From: Erwin Brandstetter X-Newsgroups: pgsql.admin Subject: Re: get the array content whis the select clause Date: Wed, 06 Jun 2007 08:48:35 -0700 Organization: http://groups.google.com Lines: 34 Message-ID: <1181144915.299010.239270@p77g2000hsh.googlegroups.com> References: <46647A79.3020401@eurofer.be> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable X-Complaints-To: groups-abuse@google.com In-Reply-To: User-Agent: G2/1.0 X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.4) Gecko/20070515 Firefox/2.0.0.4,gzip(gfe),gzip(gfe) Complaints-To: groups-abuse@google.com Injection-Info: p77g2000hsh.googlegroups.com; posting-host=62.116.9.170; posting-account=motVOw0AAAAl5BtLWL3QN4EcB1CKG8U3 To: pgsql-admin@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200706/43 X-Sequence-Number: 25711 On Jun 5, 12:13 pm, kristo.k...@skype.net (Kristo Kaiv) wrote: > i guess this what you meant: > (not a nice solution though) writing a function that returns the set > would be a better idea > > create table testintarr (iarr int[]); > insert into testintarr values ('5,6,7,8'); > > test=3D# select iarr[idx] from (select iarr, generate_series(array_lower > (iarr,1), array_upper(iarr,1)) as idx from testintarr) asdf; > iarr Function would look like this: CREATE OR REPLACE FUNCTION f_intarr_to_set(integer[]) RETURNS SETOF integer AS $BODY$ BEGIN FOR i IN array_lower($1,1) .. array_upper($1,1) LOOP RETURN NEXT $1[i]; END LOOP; END; $BODY$ LANGUAGE 'plpgsql'; SELECT * FROM f_intarr_to_set('{1,3,5,7,9,321}'); But I think I like Kristo=B4s query even better. :) Regards Erwin