Creating a CURSOR in PostgreSQL

Craig Ringer
Craig Ringer

Creating a cursor is possible in PostgreSQL, but declarative logic is almost always faster and easier to maintain.

Beware when reading the PostgreSQL documentaton that the syntax for plpgsql cursors and SQL cursors is different, but looks quite similar. Check if you're looking at the plpgsql documentation or the SQL documentation.

Here is a simple boilerplate for how to create a cursor in PostgreSQL.

DO $$
DECLARE cur_row RECORD;
BEGIN
FOR cur_row IN SELECT * FROM pg_stat_activity
LOOP
RAISE NOTICE USING MESSAGE = cur_row.query::text;
END LOOP;
END;
$$ LANGUAGE plpgsql;

If you would like to return some rows, you would have to make that a function:

CREATE OR REPLACE FUNCTION show_activity() RETURNS SETOF pg_stat_activity
AS $$
DECLARE cur_row RECORD;
BEGIN
FOR cur_row IN SELECT * FROM pg_stat_activity
LOOP
RETURN NEXT cur_row;
END LOOP;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM show_activity();

However, it would probably be much easier to just return the recordset directly, like so:

CREATE OR REPLACE FUNCTION show_activity() RETURNS SETOF pg_stat_activity
AS $$
BEGIN
RETURN QUERY SELECT * FROM pg_stat_activity;
END $$
LANGUAGE plpgsql;

Or, even simpler in SQL:

CREATE OR REPLACE FUNCTION show_activity() RETURNS SETOF pg_stat_activity
AS $$
SELECT * FROM pg_stat_activity;
$$ LANGUAGE SQL;

Declarative logic in PostgreSQL is generally much faster and easier to write. If you could give us a better idea of the problem you are trying to solve, we could help with the strategy selection and structure.

Was this article helpful?

0 out of 0 found this helpful