Cursors

A cursor is a database object that encapsulates a query and lets you retrieve its rows one at a time. In PL/pgSQL, the query FOR loop handles most use cases automatically, but explicit cursors give you more control.

Implicit Cursors via SELECT INTO

The simplest way to retrieve a single row:

DO $$
DECLARE
  v_name TEXT;
  v_sal  NUMERIC;
BEGIN
  SELECT first_name, salary
  INTO   v_name, v_sal
  FROM   employees
  WHERE  id = 1;

  IF FOUND THEN
    RAISE NOTICE '% earns %', v_name, v_sal;
  ELSE
    RAISE NOTICE 'Not found';
  END IF;
END;
$$;

The special variable FOUND is TRUE after a SELECT INTO that returned a row, and FALSE if no row was found.

Declaring an Explicit Cursor

DECLARE
  cur_employees CURSOR FOR
    SELECT id, first_name, salary FROM employees WHERE department = 'Sales';

Open, Fetch, Close

DO $$
DECLARE
  cur CURSOR FOR SELECT first_name, salary FROM employees;
  rec RECORD;
BEGIN
  OPEN cur;
  LOOP
    FETCH cur INTO rec;
    EXIT WHEN NOT FOUND;
    RAISE NOTICE '% : %', rec.first_name, rec.salary;
  END LOOP;
  CLOSE cur;
END;
$$;

After each FETCH, FOUND is TRUE if a row was retrieved, FALSE when no more rows remain.

Cursor FOR Loop (Recommended)

The FOR loop over a query opens, fetches, and closes automatically:

DO $$
BEGIN
  FOR rec IN SELECT first_name, salary FROM employees LOOP
    RAISE NOTICE '% : %', rec.first_name, rec.salary;
  END LOOP;
END;
$$;

This is preferred over manual OPEN/FETCH/CLOSE for most use cases.

Cursor Variables (Refcursors)

A refcursor can be returned from a function, allowing the caller to fetch rows:

CREATE OR REPLACE FUNCTION get_employees()
RETURNS refcursor LANGUAGE plpgsql AS $$
DECLARE
  cur refcursor;
BEGIN
  OPEN cur FOR SELECT id, first_name FROM employees;
  RETURN cur;
END;
$$;

Parameterised Cursors

DECLARE
  cur CURSOR (p_dept TEXT) FOR
    SELECT first_name FROM employees WHERE department = p_dept;
BEGIN
  OPEN cur('Sales');
  -- fetch …
  CLOSE cur;

Scrollable Cursors

By default, cursors are forward-only. A scrollable cursor allows FETCH PRIOR, FETCH FIRST, and FETCH ABSOLUTE:

DECLARE
  cur SCROLL CURSOR FOR SELECT * FROM employees;

MOVE

Advance the cursor position without retrieving a row:

MOVE NEXT FROM cur;
MOVE FORWARD 5 FROM cur;
MOVE LAST FROM cur;