Search Postgresql Archives

Re: Looping through arrays

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

              http://archives.postgresql.org

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux