Обсуждение: get the array content whis the select clause

Hello all

It is possible to return the content of an int array [] with a select
clause ?

Thanks in advance for your respons

Guy

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=# select iarr[idx] from (select iarr, generate_series(array_lower
(iarr,1), array_upper(iarr,1)) as idx from testintarr) asdf;
iarr
------
     5
     6
     7
     8
(4 rows)

BR,
Kristo

On 04.06.2007, at 23:47, De Leeuw Guy wrote:

> Hello all
>
> It is possible to return the content of an int array [] with a select
> clause ?
>
> Thanks in advance for your respons
>
> Guy
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that
> your
>        message can get through to the mailing list cleanly

Great !!!

Many thanks Kristo, it's just what I need

Guy

Kristo Kaiv a écrit :
> 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=# select iarr[idx] from (select iarr,
> generate_series(array_lower(iarr,1), array_upper(iarr,1)) as idx from
> testintarr) asdf;
> iarr
> ------
>     5
>     6
>     7
>     8
> (4 rows)
>
> BR,
> Kristo
>
> On 04.06.2007, at 23:47, De Leeuw Guy wrote:
>
>> Hello all
>>
>> It is possible to return the content of an int array [] with a select
>> clause ?
>>
>> Thanks in advance for your respons
>>
>> Guy
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 1: if posting/reading through Usenet, please send an appropriate
>>        subscribe-nomail command to majordomo@postgresql.org so that your
>>        message can get through to the mailing list cleanly
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq

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=# 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�s query even better. :)


Regards
Erwin