CODEWITHSUNDEEP
arrayspostgresqlplpgsql
Leajon
Leajon

Reputation: 219

How to use an array loop in pgSQL

I want to do something like this:

CREATE OR REPLACE FUNCTION ff(int, text) RETRUNS integer AS $$
DECLARE
    r text;
BEGIN
    FOR r IN SELECT string_to_array($2, ',')
    LOOP
        INSERT INTO test(i, t) VALUES($1, r);
    END LOOP;
    RETRUN 0;
END
$$LANGUAGE pgsql;

I hope that the function SELECT ff(3, 'a,b'); does

INSERT INTO test(i, t) VALUES(3, 'a'); 
INSERT INTO test(i, t) VALUES(3, 'b');

Upvotes: 5

Views: 19003

Answers (2)

Pavel V.
Pavel V.

Reputation: 2810

Since PostgreSQL 9.1 you can use FOREACH LOOP to iterate over an array. An example from documentation:

CREATE FUNCTION sum(int[]) RETURNS int8 AS $$
DECLARE
  s int8 := 0;
  x int;
BEGIN
  FOREACH x IN ARRAY $1
  LOOP
    s := s + x;
  END LOOP;
  RETURN s;
END;
$$ LANGUAGE plpgsql;

For you, mu's answer is better, but most users who find this question by its title would prefer my answer.

Upvotes: 12

mu is too short
mu is too short

Reputation: 434965

You don't need a loop for that, you could use unnest to convert the array from string_to_array into a set of rows and then use a simple insert ... select construct:

create or replace function ff(int, text) returns integer as $$
begin
    insert into test(i, t)
    select $1, s 
    from unnest(string_to_array($2, ',')) as dt(s);
    return 0;
end
$$ language plpgsql;

I've also corrected some typos (RETRUNS, RETRUN, and pgsql) along the way.

You could also use regexp_split_to_table:

create or replace function ff(int, text) returns integer as $$
begin
    insert into test(i, t)
    select $1, s
    from regexp_split_to_table($2, ',') as dt(s);
    return 0;
end
$$ language plpgsql;

If you're stuck in the 8.1 stone age and can't do anything about it, then perhaps something like this would work:

create or replace function ff(int, text) returns integer as $$
declare
    a text[]; 
    i int;
begin
    select string_to_array($2, ',') into a; 
    i := 1;
    loop  
        if i > array_upper(a, 1) then
            exit;
        else
            insert into test(i, t) values($1, a[i]);
            i := i + 1;
        end if;
    end loop;                                 
    return 0;       
end
$$ language plpgsql;

I think that should work in 8.1 but I don't have access to 8.1 to check.

Upvotes: 11

Related Questions