6

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?

2
  • Show the rest of the function. Is there a DECLARE block? Commented Apr 28, 2014 at 9:16
  • @CraigRinger: answered by and edit. Adding whole context would make me show half the code of my whole project, which would obfuscate things a lot. I made sure everything matches the corresponding views and tables, so I don't want to include these if it is not necessary.
    – Pavel V.
    Commented Apr 28, 2014 at 9:31

2 Answers 2

7

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 undeclared
1
  • You are right. I forgot this, so I even omitted the loop variable declaration for the working loop example in last update. I have corrected this now.
    – Pavel V.
    Commented Apr 28, 2014 at 9:40
1

I got the same error below:

ERROR: loop variable of FOREACH must be a known variable or list of variables

When I tried to iterate an array with a FOREACH statement as shown below:

DO $$
BEGIN
  FOREACH temp SLICE 0 IN ARRAY ARRAY['a','b','c'] LOOP
    RAISE INFO '%', temp;
  END LOOP;
END
$$;

So, I declared temp local variable as shown below, then the error was solved:

DO $$
DECLARE
  temp VARCHAR; -- Here
BEGIN
  FOREACH temp SLICE 0 IN ARRAY ARRAY['a','b','c'] LOOP
    RAISE INFO '%', temp;
  END LOOP;
END
$$;

In addition, a FOR statement doesn't need a declared local variable when iterating numbers as shown below:

DO $$
BEGIN
  FOR num IN 1..3 LOOP
    RAISE INFO '%', num;
  END LOOP;
END
$$;

While, a FOR statement must need a declared local variable when iterating table's rows as shown below otherwise there is the error:

DO $$
DECLARE
  row RECORD; -- Here
BEGIN
  FOR row IN SELECT * FROM person LOOP
    RAISE INFO '%', row;
  END LOOP;
END
$$;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.