Re: Looping through arrays

Robert Fitzpatrick wrote:
> I have a field with 'AA-BB-CC-DD' and I want to pull those four values
> into an array and then loop through the array inserting records into a
> table for each element. Can you someone point me to an example of this
> in pl/pgsql?
>

Something like this?

create table testfoo (id int, arrstr text);
create table testfoo_det (id int, elem text);
insert into testfoo values (1, 'AA-BB-CC-DD');
insert into testfoo values (2, 'EE-FF-GG-HH');

create or replace function testfoo_func(int) returns void as $$
declare
   arrinp    text[];
begin
   select into arrinp string_to_array(arrstr,'-')
   from testfoo where id = $1;

   for i in array_lower(arrinp, 1)..array_upper(arrinp, 1) loop
     execute 'insert into testfoo_det
     values (' || $1 || ', ''' || arrinp[i] || ''')';
   end loop;

   return;

end;
$$ language plpgsql;

regression=# select testfoo_func(id) from testfoo;
  testfoo_func
--------------


(2 rows)

regression=# select * from testfoo_det;
  id | elem
----+------
   1 | AA
   1 | BB
   1 | CC
   1 | DD
   2 | EE
   2 | FF
   2 | GG
   2 | HH
(8 rows)

HTH,

Joe