Thread: Accessing array elements in a FOR PL/pgsql loop

For each element in the array, I need to make some operation with plpgsql.
I usually use the syntax:

DECLARE
  array_len int;
BEGIN
  array_len := array_upper(i_array, 1);
  FOR i IN 1 .. array_len
  LOOP
     SOME OPERATION (i_array[i])
  END LOOP;

But I don't like that. Is there any built-in way to do that without
using the length, i.e like in python, e.g.
  for element in array:
    ....
This example does not work in Postgres.

I think I need a built-in function to make a column from an array, like
in the backwards operation SELECT ARRAY(column)

In response to Igor Katson :
> I think I need a built-in function to make a column from an array, like
> in the backwards operation SELECT ARRAY(column)

By David Fetter:

CREATE OR REPLACE FUNCTION unnest(ANYARRAY) RETURNS SETOF ANYELEMENT
LANGUAGE SQL AS $$SELECT $1[i] FROM
generate_series(array_lower($1,1),array_upper($1,1)) i;$$;

HTH, Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

A. Kretschmer wrote:
> In response to Igor Katson :
>
>> I think I need a built-in function to make a column from an array, like
>> in the backwards operation SELECT ARRAY(column)
>>
>
> By David Fetter:
>
> CREATE OR REPLACE FUNCTION unnest(ANYARRAY) RETURNS SETOF ANYELEMENT
> LANGUAGE SQL AS $$SELECT $1[i] FROM
> generate_series(array_lower($1,1),array_upper($1,1)) i;$$;
>
> HTH, Andreas
>
Thanks. I thought, there is a built-in one for that.

On Thu, 19 Feb 2009, Igor Katson wrote:

> A. Kretschmer wrote:
>> In response to Igor Katson :
>>
>>> I think I need a built-in function to make a column from an array, like in
>>> the backwards operation SELECT ARRAY(column)
>>>
>>
>> By David Fetter:
>>
>> CREATE OR REPLACE FUNCTION unnest(ANYARRAY) RETURNS SETOF ANYELEMENT
>> LANGUAGE SQL AS $$SELECT $1[i] FROM
>> generate_series(array_lower($1,1),array_upper($1,1)) i;$$;
>>
>> HTH, Andreas
>>
> Thanks. I thought, there is a built-in one for that.
>

there is, but in CVS HEAD
psql (8.4devel)
Type "help" for help.

postgres=# select unnest('{1,2,3}'::int[]);
  unnest
--------
       1
       2
       3
(3 rows)



     Regards,
         Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83