psql - loop variable of loop over rows must be a record or row variable or list of scalar variables

Question

Here is my simple anonymous code block:

do $$
  declare foo varchar(50) := '';
  begin
    for a in
      select a from (values('foo'), ('bar'), ('fooBar')) s(a)
    loop
      foo := a;
      print foo;
    end loop;
  end;
$$;

When I run it:

psql -f test.sql

I get this error:

psql:test.sql:11: ERROR:  loop variable of loop over rows must be a record or row variable or list of scalar variables
LINE 4:     for a in
            ^

Pepillo

Answer 1

Solved it myself, meh. Needed to declare arow record.

do $$
  declare
    arow record;
    foo varchar(50);
  begin
    for arow in
      select a from (values('foo'), ('bar'), ('fooBar')) s(a)
    loop
      foo := arow.a;
      RAISE NOTICE 'Calling cs_create_job(%)', foo;
    end loop;
  end;
$$;

Pepillo

Thanks saved my day. Can you just remove that s(a), it confused me for a while :( – Succumb

Answer 2

I got the same error below:

ERROR: loop variable of loop over rows must be a record variable or list of scalar variables

When I tried to iterate table's rows with a FOR statement as shown below:

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

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

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

In addition, a FOREACH statement must need a declared local variable as shown below, otherwise there is the error:

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

Diffidence

Answer 3

How to retrieve the error Loop variable of loop over rows must be a record variable or list of scalar variables Line : For c_ rec IN (select datasourcenm,

Beverlybevers

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center. – Chlodwig