Looping through arrays

On Thu, Nov 03, 2005 at 06:15:08PM -0500, 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?

See "Array Functions and Operators" in the documentation for some
useful functions:

http://www.postgresql.org/docs/8.0/interactive/functions-array.html

Here's a simple example:

CREATE TABLE foo (
id serial PRIMARY KEY,
val text NOT NULL
);

CREATE FUNCTION splitinsert(str text, sep text) RETURNS void AS $$
DECLARE
i integer;
a text[];
BEGIN
a := string_to_array(str, sep);

FOR i IN array_lower(a, 1) .. array_upper(a, 1) LOOP
INSERT INTO foo (val) VALUES (a[i]);
END LOOP;

RETURN;
END;
$$ LANGUAGE plpgsql VOLATILE STRICT;

SELECT splitinsert('AA-BB-CC-DD', '-');

SELECT * FROM foo;
id | val
----+-----
1 | AA
2 | BB
3 | CC
4 | DD
(4 rows)

--
Michael Fuhr

On Thu, Nov 03, 2005 at 06:15:08PM -0500, 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?

You can do it in SQL, at least in 8.0 and later :)

INSERT INTO bar(blurf)
SELECT (string_to_array('AA-BB-CC-DD','-'))[s.i] AS "foo"
FROM generate_series(
array_lower(string_to_array('AA-BB-CC-DD','-'),1),
array_upper(string_to_array('AA-BB-CC-DD','-'),1)
) AS s(i);

Cheers,
D
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778

Remember to vote!

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