Menu
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 ^ 
882
The syntax of the for loop statement to iterate over a result set of a dynamic query: [ <<label>> ] for row in execute query_expression [ using query_param [, ... ] ] loop statements end loop [ label ];
In PostgreSQL, we can also use a for loop without creating a function. Instead, we will create an anonymous block and in the block, we will define a for loop. In the above code, first, we created an anonymous block and in the block, we created an array variable with some values.
PostgreSQL uses record type variables which simply act as placeholders for rows of a result set, similar to a row type variable. However, unlike row type variables, they do not have a predefined structure. Their structure is only determined after assigning a row to them.
The general syntax of a variable declaration is: name [ CONSTANT ] type [ COLLATE collation_name ] [ NOT NULL ] [ { DEFAULT | := | = } expression ];
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; $$; If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With