Thread: Looping through arrays

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?

--
Robert

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