Re: Looping through arrays - Mailing list pgsql-general

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