I have few FOREACH loops in my functions. The loops seem to perfectly agree with the official postgresql documentation. This error message is returned:
ERROR: loop variable of FOREACH must be a known variable or list of variables
SQL state: 42601
Google found nothing for postgresql "loop variable of FOREACH"
. I solved this somehow for my older foreach loops, but I don't remember how, and I can't find any difference that could be significant for the solution of this error.
I have PostgreSQL 9.3.4 and pgAdmin III 1.18.1 on Windows XP 32 bit. Here are interresting parts of the functions:
CREATE OR REPLACE FUNCTION vloz_kont() RETURNS trigger AS $$
DECLARE
_idmat integer;
_nazmat text;
_zast text;
sj_nad text;
...
BEGIN
...
--bugged loop
FOREACH _mat IN ARRAY NEW._material::text[] LOOP
SELECT split_part( _mat, ' ', 1 ) INTO _nazmat;
SELECT rtrim( ltrim(split_part( _mat, ' ', 2 ), '(' ), ')' ) INTO _zast;
SELECT id INTO _idmat FROM pro_material WHERE popis = _nazmat;
IF _idmat IS NOT NULL THEN
INSERT INTO material_konst ( id_mat, kod_sj, kod_akce, zastoupeni )
VALUES ( _idmat, NEW._kod_sj, NEW._kod_akce, _zast );
ELSE
INSERT INTO material_konst ( id_mat, kod_sj, kod_akce, jiny_nazev, zastoupeni )
VALUES ( 8, NEW._kod_sj, NEW._kod_akce, _nazmat, _zast );
END IF;
END LOOP;
...
--OK LOOP
FOREACH sj_nad IN ARRAY NEW._nad::text[] LOOP
INSERT INTO s_vztah (nad, pod, kod_akce, typ_vztahu)
VALUES(
sj_nad,
NEW._kod_sj,
NEW._kod_akce,
1
);
END LOOP;
...
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
Both _material
and _nad
are text array columns of a view, as well as all other variables. The whole INSTEAD OF trigger function is too long to reproduce in entirety (almost 200 code rows) - ...
in the code sample represent skipped unrelated code.
So why is there the error, and how to solve it?
DECLARE
block?