PostgreSQL cursors PostgreSQL cursors sql sql

PostgreSQL cursors


You very rarely want to explicitly use cursors at all in PostgreSQL, even when processing query results in plpgsql. This is a marked contrast from many other SQL databases where they are used almost all the time.

In plpgsql you could simply write something like:

DECLARE  emp employee%rowtype;BEGIN  FOR emp IN SELECT * FROM employee LOOP    IF emp.active THEN      RAISE INFO 'Active: %', emp.employee_id    END IF;  END LOOP;END

In the above, the plpgsql language handler deals with opening, binding, fetching and closing itself (more on declarations, and control structures).

With PostgreSQL from 9.0, you can simply drop into executing plpgsql using a "DO" block. For prior versions, you need to create a function and select it. If you're looking for the PostgreSQL equivalent of, for example, iterating through a result with a cursor on SQL Server, that's what it is. Note that iteration etc. are not part of the SQL dialect, only part of plpgsql (or any of the other emebedded languages).

The "DECLARE CURSOR xxx" syntax at the SQL level can be used like this:

DECLARE cur_employees CURSOR FOR SELECT * FROM employee;FETCH NEXT FROM cur_employees;// etc..CLOSE cur_employees;

This can be used to carefully get just part of the query result set. However, it's unusual to use these, since usually your client driver will provide some sort of functionality to do this (e.g. scrollable result sets in JDBC). You can also return cursors from functions similar to Oracle, although again that's a comparatively rare use case.


Generally, one has a DECLARE for the cursor, then an OPEN of the cursor (which materializes the result set), multiple FETCH operations to retrieve the rows from the result set individually, and then one does a CLOSE of the cursor.

You appear to have a DECLARE followed by a CLOSE. Thus your syntax error, as you never did an OPEN.