PL/pgSQL "for loop" + select basic example ("hello world")

Question

I've been using Postgres for a while, but I'm totally new to PL/pgSQL.

I'm struggling to get a basic for loop to work.

This works fine:

-- Without SELECT
DO $$
BEGIN 
  FOR counter IN 1..6 BY 2 LOOP
    RAISE NOTICE 'Counter: %', counter;
  END LOOP;
END; $$;

But what I really want is to iterate through the result of a SELECT query.

I keep running into this error:

Error in query: ERROR: loop variable of loop over rows must be a record or row variable or list of scalar variables

Sounds pretty obscure to me and googling did not help.

There's a table from my own data I want to use (I was hoping to use a SELECT * FROM mytable WHERE ‹whatever›), but I realize I can't even get the for loop to work with simpler data.

Take this:

-- with a SELECT
DO $$
BEGIN 
RAISE NOTICE 'Get ready to be amazed…';
FOR target IN SELECT * FROM generate_series(1,2) LOOP
    RAISE NOTICE 'hello'
END LOOP;
END; $$

This generates the error above too. I'd like to get a simple thing printed to get the hang of the loop syntax, something like:

hello 1
hello 2

What am I doing wrong?

Answer 1

The iterator must be declared

DO $$
DECLARE 
    target record;
BEGIN 
    RAISE NOTICE 'Get ready to be amazed…';
    FOR target IN SELECT * FROM generate_series(1,2) LOOP
        RAISE NOTICE 'hello';
    END LOOP;
END; $$;

NOTICE:  Get ready to be amazed…
NOTICE:  hello
NOTICE:  hello