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? 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.
    Apr 28, 2014 at 9:31

1 Answer 1

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.
    Apr 28, 2014 at 9:40

Your Answer

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

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