PL/pgSQL Cursor

Comprehensive introduction to PL/pgSQL cursors — implicit cursors that PostgreSQL creates automatically for SELECT INTO and FOR loops, the FOUND variable for zero-row checks, and the critical correction that plain SELECT INTO does NOT raise on multiple rows in PostgreSQL (that's MySQL behavior).

A cursor in PL/pgSQL is a pointer into the result set of a query. Where a single SELECT INTO hands you exactly one row's worth of data, a cursor lets you walk through any number of rows one at a time, processing each before moving to the next.

PostgreSQL supports two flavors:

This page focuses on implicit cursors — the ones PostgreSQL creates and manages for you behind the scenes. You'll write code that uses them every day without thinking about it; understanding what's happening under the hood helps debug edge cases.

PostgreSQL creates implicit cursors automatically whenever you run a SQL statement inside a PL/pgSQL block. You never declare them, never open them, never close them — that's all handled for you. The four operations that trigger one:

The most common case: pull a single row's data into variables.

DO $$
DECLARE
    v_name VARCHAR;
BEGIN
    SELECT name INTO v_name
    FROM   employees_20
    WHERE  id = 1003;

    RAISE NOTICE 'Found: %', v_name;
END
$$;

Behind the scenes, PostgreSQL opened a cursor for the SELECT, fetched the one row, assigned its values into v_name, then closed the cursor — all invisibly.

Same automatic cursor management for write operations:

DO $$
BEGIN
    INSERT INTO employees_20 (id, name, department_id) VALUES (1099, 'Test User', 99);
    UPDATE employees_20 SET department_id = 88 WHERE id = 1099;
    DELETE FROM employees_20 WHERE id = 1099;
END
$$;

Each of these statements works through an implicit cursor that PostgreSQL manages on your behalf. You don't see it; you don't have to.

Set up a table the rest of the examples will use:

DROP TABLE IF EXISTS employees_20;

CREATE TABLE employees_20 (
    id            INT PRIMARY KEY,
    name          VARCHAR(100),
    department_id INT
);

INSERT INTO employees_20 (id, name, department_id) VALUES
    (1001, 'Jane Smith',      10),
    (1002, 'Michael Johnson', 20),
    (1003, 'Emily Williams',  20),
    (1004, 'David Brown',     30),
    (1005, 'Sarah Davis',     30),
    (1006, 'James Miller',    40),
    (1007, 'Emma Wilson',     40),
    (1008, 'William Taylor',  50),
    (1009, 'John Doe',        10),
    (1010, 'Olivia Martinez', 50);

The behavior of SELECT INTO when the row count isn't exactly one is a common source of confusion — and PostgreSQL differs from Oracle and MySQL here.

The FOUND special variable tells you whether the most recent statement matched anything. The cleanest way to handle "row may not exist":

DO $$
DECLARE
    emp_record employees_20%ROWTYPE;
BEGIN
    SELECT * INTO emp_record
    FROM   employees_20
    WHERE  id = 9999;     -- no such employee

    IF NOT FOUND THEN
        RAISE NOTICE 'No employee with id 9999';
    ELSE
        RAISE NOTICE 'Found: %', emp_record.name;
    END IF;
END
$$;

If a query could return multiple rows but you only want one, add ORDER BY ... LIMIT 1 — that gives you a deterministic pick. The original tutorial used LIMIT 1 alone, but without an ORDER BY, "first" is undefined.

DO $$
DECLARE
    emp_record employees_20%ROWTYPE;
BEGIN
    SELECT * INTO emp_record
    FROM   employees_20
    WHERE  department_id = 50
    ORDER  BY id
    LIMIT  1;

    RAISE NOTICE 'First employee in dept 50: %', emp_record.name;
END
$$;

For processing many rows, the most idiomatic PL/pgSQL pattern is FOR record IN SELECT ... LOOP. PostgreSQL implicitly opens a cursor, fetches each row in turn, and closes the cursor when the loop ends. No explicit DECLARE / OPEN / FETCH / CLOSE needed.

DO $$
DECLARE
    emp_rec record;
BEGIN
    FOR emp_rec IN
        SELECT id, name FROM employees_20 WHERE department_id = 30
    LOOP
        RAISE NOTICE 'Employee % - %', emp_rec.id, emp_rec.name;
    END LOOP;
END
$$;

Two rows in department 30, two NOTICE lines, no manual cursor work. The record type adapts to whatever columns the SELECT returned — you can also use a %ROWTYPE variable when the shape is fixed.

Combine the iteration with an accumulator variable to compute summaries during the walk:

DO $$
DECLARE
    emp_rec    record;
    total_emps INTEGER := 0;
BEGIN
    FOR emp_rec IN
        SELECT name FROM employees_20 ORDER BY id
    LOOP
        total_emps := total_emps + 1;
    END LOOP;

    RAISE NOTICE 'Walked through % employees', total_emps;
END
$$;

For the vast majority of use cases, implicit cursors via FOR ... IN ... LOOP are the right tool. Reach for explicit cursors only when implicit ones can't express what you need.

1. Reach for FOR rec IN SELECT ... LOOP as the default pattern for iterating rows. It's the cleanest, most readable form and lets PostgreSQL handle cursor lifecycle.
2. Add ORDER BY ... LIMIT 1 when you want a deterministic single row from a possibly-multi-row query. LIMIT 1 alone gives you "some row" — not always the same one.
3. Use SELECT INTO STRICT when the query should return exactly one row and anything else is a bug. Loud failure beats silent wrong-data.
4. Check FOUND after a plain SELECT INTO when zero rows is a valid case you need to handle.
5. Don't loop over rows just to compute aggregates — let the SQL engine do SUM, AVG, COUNT for you. Loops are for per-row processing that pure SQL can't express.
6. Beware Oracle-isms. PL/pgSQL doesn't have NO_DATA_FOUND raised automatically (only with STRICT); doesn't have %ROWCOUNT as a cursor attribute (use FOUND or GET DIAGNOSTICS instead).

• A cursor is a pointer into a query's result set, used for row-by-row processing.
• PostgreSQL creates implicit cursors automatically for SELECT INTO, INSERT, UPDATE, DELETE, and FOR rec IN SELECT ... LOOP. You don't manage them.
• The FOR loop over a SELECT is the idiomatic pattern for walking rows — clean, lifecycle-managed, no boilerplate.
• Plain SELECT INTO: 0 rows → variables NULL and FOUND false; 1 row → assigned; 2+ rows → picks first arbitrarily, no error. Use STRICT to make multi-row a hard error.
• The FOUND special variable tells you whether the latest statement matched anything.
• Use explicit cursors only when implicit ones can't express what you need — covered on the next pages.