Re: 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