CURSOR vs select statement in loop

Question

I just saw a simple example in another StackOverflow question that used a cursor to loop through a table. I would have just looped through the results of a select query instead of wrapping the select query in a cursor. What is the advantage of using a cursor?

(I couldn't include the example here because StackOverflow thought my question was mostly code, and demanded more details. I've run into that annoying restriction before. If I can ask my question clearly in just a few words, I should be able to. I'll see if I can find a link to that question, and if I can, I'll add the link here.)

Here is the original question where I saw CURSOR used.

klin · Answer 1 · 2018-07-30T15:55:45.760

What is the advantage of using a cursor?

The only advantage is that you have to write more code (if they pay you for each line of code).

do $$
declare
    rec record;
    cur cursor for select i from generate_series(1, 3) i;
begin
    open cur;
    loop
        fetch cur into rec;
        exit when rec is null;
        raise notice '%', rec.i;
    end loop;
    close cur;
end
$$;

A loop through query results just opens a (virtual) cursor, fetches rows, checks range, exits when needed and closes the cursor for you.

do $$
declare
    rec record;
begin
    for rec in select i from generate_series(1, 3) i
    loop
        raise notice '%', rec.i;
    end loop;
end
$$;

score 5 · Answer 2 · answered Jul 30 '18 at 15:58

There are several ways:

Use an explicit cursor in PL/pgSQL and loop through it and process each result row.

Example:

OPEN c FOR SELECT id FROM a WHERE ok;
LOOP
   UPDATE b SET a_ok = TRUE WHERE a_id = c.id;
END LOOP;

Use a FOR r IN SELECT ... LOOP in PL/pgSQL. This is effectively the same as 1. with a clearer syntax.

Example:
```
FOR c IN SELECT id FROM a WHERE ok LOOP
   UPDATE b SET a_ok = TRUE WHERE a_id = c.id;
END LOOP;
```

Run a SELECT query without a cursor and process each result row on the client side, probably issuing a database query for each result.

Example (in pseudocode):

resultset := db_exec('SELECT id FROM a WHERE ok');
while (resultset.next()) {
    db_exec('UPDATE b SET a_ok = TRUE WHERE a_id = ' || resultset.get('id'));
}

Use a JOIN.

Example:

UPDATE b SET a_ok = TRUE
FROM a
WHERE a.id = b.a_id AND a.ok;

Method 3. is the most terrible way conceivable to solve the problem, because it causes a lot of client-server round trips and has the database parse a gazillion statements. Alas, it is often the way how SQL novices attack the problem. I call it home-grown nested loop join. On top of all that, the client software will often snarf the complete result set from the first query into memory, which causes yet another problem.

Methods 1. and 2. are equivalent, except that 2. is more elegant. It saves the round trips and uses prepared statements under the hood, so the UPDATE doesn't have to be parsed all the time. Still, the executor has to run many times, and PL/pgSQL is known not to be particularly fast. It is also a kind of home-grown nested loop join.

Method 4 is the way to go. Not only is everything run in a single query, but PostgreSQL can also use a more effective join strategy if that is better.

CURSOR vs select statement in loop

2 Answers2