debugsqlMinor
ERROR: loop variable of FOREACH must be a known variable or list of variables
Viewed 0 times
errormustloopknownvariablesforeachlistvariable
Problem
I have few FOREACH loops in my functions. The loops seem to perfectly agree with the official postgresql documentation. This error message is returned:
Google found nothing for
I have PostgreSQL 9.3.4 and pgAdmin III 1.18.1 on Windows XP 32 bit. Here are interresting parts of the functions:
Both
So why is there the error, and how to solve it?
ERROR: loop variable of FOREACH must be a known variable or list of variables
SQL state: 42601Google 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?
Solution
Without seeing complete code, or a cut-down test case, it's hard to be sure but the error strongly suggests that:
FOREACH _mat
^^^^
this variable is undeclaredCode Snippets
FOREACH _mat
^^^^
this variable is undeclaredContext
StackExchange Database Administrators Q#64046, answer score: 7
Revisions (0)
No revisions yet.