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
