A basic script to loop over an array in psql-
CREATE OR REPLACE FUNCTION func1(n character varying, v character varying)
RETURNS integer AS
$BODY$
DECLARE
return_code integer;
BEGIN
RAISE NOTICE '(%,%)', n, v;
return_code := 1;
RETURN return_code;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN -1;
END;
$BODY$
LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION func2()
RETURNS integer AS
$BODY$
DECLARE
return_code integer;
pairs varchar[][] := array[['key2','val2'],
['key1','val2']];
BEGIN
FOR i IN array_lower(pairs, 1) .. array_upper(pairs, 1)
LOOP
--RAISE NOTICE '%,%',pairs[i][1]::varchar, pairs[i][2]::varchar;
PERFORM func1(pairs[i][1]::varchar, pairs[i][2]::varchar);
END LOOP;
return 1;
END;
$BODY$
LANGUAGE plpgsql;
SELECT * from func2();
1 comment:
from stackoverflow-
Since PostgreSQL 9.1 there is the very convenient (and fast) FOREACH you can:
DO
$BODY$
DECLARE
m varchar[];
arr varchar[][] := array[['key1','val1'],['key2','val2']];
BEGIN
FOREACH m SLICE 1 IN ARRAY arr
LOOP
RAISE NOTICE 'another_func(%,%)',m[1], m[2];
END LOOP;
END;
$BODY$ language plpgsql
Post a Comment