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');
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.
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.
returns void. What is that dt(s) ?
dt(s) is the table alias with a column name. And we can't know if the return 0 is useless or not with more context, there could be all sorts of good reasons for this function to return zero.