PostgreSQL already has different methods to access and update data, like SELECT, UPDATE, DELETE, and other procedural operations with PL/pgSQL. In almost all cases, a query processes different rows as a single operation. In some applications, we need to process one row at a time with additional logic added to each one as we proceed.
A cursor allows us to execute a query and get its resulting rows incrementally. That means instead of processing the entire result set at once, a cursor will maintain a position within the result set and let the caller fetch rows as needed.
Why Do We Have Cursors in PostgreSQL?
Let’s think of a table having thousands of account transactions. Suppose we have to check each transaction, apply any custom business logic, and update that row. We might have to get all the matching rows first into the application’s memory and process each individually. This will consume more memory and require additional applications for database connections.
A cursor provides an alternative way that allows each row to be retrieved incrementally. We will explain how to do it with an example here.
Cursors can do the following operations:
- Get and process one row or a small batch of rows at a time.
- You can avoid the process of getting the entire large result into the application memory when you need to process each row separately.
- Perform additional calculations or rules for each row.
- To update or delete the row at the current position of the cursor with WHERE CURRENT OF.
How Performing the Operation Without a Cursor Might Look Like
Let’s consider an example; we will use these tables to perform the operations on:
CREATE TABLE customer_credit (
customer_id INT PRIMARY KEY,
available_credit NUMERIC(12, 2) NOT NULL
);
CREATE TABLE payment_requests (
id INT PRIMARY KEY,
customer_id INT NOT NULL,
amount NUMERIC(12, 2) NOT NULL,
created_at TIMESTAMP NOT NULL,
status TEXT NOT NULL DEFAULT 'pending'
);
Now, let's insert some sample records:
INSERT INTO customer_credit
VALUES (1, 1000.00);
INSERT INTO payment_requests
(id, customer_id, amount, created_at)
VALUES
(101, 1, 400, '2026-10-17 09:00:00'),
(102, 1, 350, '2026-10-17 09:01:00'),
(103, 1, 300, '2026-10-17 09:02:00'),
(104, 1, 200, '2026-10-17 09:03:00'),
(105, 1, 100, '2026-10-17 09:04:00');
In this example, we are processing payments against customer invoices. Each customer will have a limited credit available, payments will arrive in a specific order, and each payment will be accepted or rejected based on remaining credit. We need to update the payment status, adjust the customer’s available credit, and record the outcome of each decision.
The important thing is that each decision depends on the credit remaining after the previous approved request.
Let’s check how we can do this without a cursor:
First, lock the customer's credit row to prevent another transaction from processing the same customer's credit at the same time using FOR UPDATE:
SELECT available_credit
FROM customer_credit
WHERE customer_id = 1
FOR UPDATE;
Next, we will calculate the payment decision; the challenge we face here is that the decision we have to make depends on the remaining balance from the previous payment. Will have to use a recursive CTE to carry that balance from one row (previous transaction result) to the next.
CREATE TEMP TABLE payment_decisions
ON COMMIT DROP
AS
WITH RECURSIVE ordered_payments AS (
SELECT id, customer_id, amount, created_at,
ROW_NUMBER() OVER (ORDER BY created_at, id) AS rn
FROM payment_requests
WHERE customer_id = 1
AND status = 'pending'
),
decisions (rn, id, amount, remaining_before, payment_status, remaining_after) AS (
-- First payment: compare against the starting credit
SELECT p.rn,
p.id,
p.amount,
c.available_credit::numeric(12,2),
(CASE WHEN p.amount <= c.available_credit
THEN 'approved' ELSE 'rejected' END)::text,
(CASE WHEN p.amount <= c.available_credit
THEN c.available_credit - p.amount
ELSE c.available_credit END)::numeric(12,2)
FROM ordered_payments p
JOIN customer_credit c ON c.customer_id = p.customer_id
WHERE p.rn = 1
UNION ALL
-- Next payment: compare against the credit left by the previous one
SELECT p.rn,
p.id,
p.amount,
d.remaining_after,
(CASE WHEN p.amount <= d.remaining_after
THEN 'approved' ELSE 'rejected' END)::text,
(CASE WHEN p.amount <= d.remaining_after
THEN d.remaining_after - p.amount
ELSE d.remaining_after END)::numeric(12,2)
FROM decisions d
JOIN ordered_payments p ON p.rn = d.rn + 1
)
SELECT * FROM decisions;
How it works:
- ordered_payments numbers the pending payments in the processing order.
- The first branch evaluates payment 1 against the starting credit.
- The recursive branch evaluates payment n+1 using remaining_after from payment n.
- Approved payments reduce the balance, and rejected ones leave it unchanged.
Check the results:
SELECT * FROM payment_decisions ORDER BY rn;
You can now apply the decisions with set-based updates:
UPDATE payment_requests p
SET status = d.payment_status
FROM payment_decisions d
WHERE p.id = d.id;
UPDATE customer_credit c
SET available_credit = d.remaining_after
FROM payment_decisions d
WHERE c.customer_id = 1
AND d.rn = (SELECT MAX(rn) FROM payment_decisions);
COMMIT;
Now, let’s see how we do this with cursors:
First, we will set the row lock with FOR UPDATE as we did for the above case without using cursors, and let’s process each payment at a time:
DO
DECLARE
remainingcreditnumeric(12,2);
paymentcursorCURSORFOR
SELECTid,amount
FROMpaymentrequests
WHEREcustomerid=1
ANDstatus='pending'
ORDERBYcreatedat,id
FORUPDATE;
paymentrecordRECORD;
BEGIN
SELECTavailablecredit
INTOremainingcredit
FROMcustomercredit
WHEREcustomerid=1;
OPENpaymentcursor;
LOOP
FETCHNEXTFROMpaymentcursorINTOpaymentrecord;
EXITWHENNOTFOUND;
IFpaymentrecord.amount<=remainingcreditTHEN
remainingcredit:=remainingcredit-paymentrecord.amount;
UPDATEpaymentrequests
SETstatus='approved'
WHERECURRENTOFpaymentcursor;
ELSE
UPDATEpaymentrequests
SETstatus='rejected'
WHERECURRENTOFpaymentcursor;
ENDIF;
ENDLOOP;
CLOSEpaymentcursor;
UPDATEcustomercredit
SETavailablecredit=remainingcredit
WHEREcustomerid=1;
END;
;
COMMIT;
Now, this is how the cursor approach works:
- OPEN payment_cursor will select the pending payments in chronological order.
- FETCH NEXT retrieves one payment and advances the cursor.
- If there is sufficient credit: approve the payment and deduct the amount.
- In case of Insufficient credit: reject the payment and keep the balance unchanged.
- UPDATE ... WHERE CURRENT OF payment_cursor modifies exactly the row the cursor is positioned on.
- The loop repeats until FETCH returns no row. The remaining balance is then written back to customer_credit.
So that was an example of where cursors would help us. Now let’s see different cursor operations and basic commands we need to know when using cursors.
1. Declaring and Opening a Cursor
A cursor can be defined with the query below and can be opened before fetching rows.
DECLARE payment_cursor CURSOR FOR
SELECT id, amount
FROM payment_requests;
OPEN payment_cursor;
2. Fetching Rows
There are several options to fetch rows from a cursor. First, using NEXT: this will fetch the next row:
FETCH NEXT FROM payment_cursor;
The option below using PRIOR will get the previous row. Example:
FETCH PRIOR FROM payment_cursor;
The next one using FORWARD will fetch the upcoming rows in the forward direction.
FETCH FORWARD 5 FROM payment_cursor;
This retrieves up to five rows in the forward direction.
3. Using Cursor Parameters
We can use parameters along with cursors to filter the rows it retrieves. For example:
DECLARE payment_cursor CURSOR (cust_id INT) FOR
SELECT id, amount
FROM payment_requests
WHERE customer_id = cust_id;
This is how you will open that cursor using a parameter:
OPEN payment_cursor(1);
This retrieves payment requests belonging to customer 1.
4. Closing a Cursor
Once all the processing of a cursor is complete, we can close the cursor to release its resources.
CLOSE payment_cursor;
Cursors will normally exist only for the duration of the current transaction within which they are created. They are automatically closed when the transaction ends, unless they are declared with WITH HOLD.
When Should You Use Cursors?
Cursors are useful when you need to process query results incrementally, apply different logic to individual rows, or update the current row using WHERE CURRENT OF.
However, do not use cursors for simple operations that can be completed using a single SQL statement; set-based queries are generally preferred because they can avoid unnecessary processing of each row.
Be careful with WHERE CURRENT OF and using it with an unsuitable cursor that might update or delete incorrect rows in a table. Long-running transactions can retain table and row locks until the transaction ends, potentially blocking other operations and consuming database resources. Closing a cursor alone does not release these locks.
Cursors in PostgreSQL can provide a way to fetch query results incrementally and can control the way individual rows are processed. If you are to use cursors, understanding, declaring, opening, fetching, the use of parameters, and the lifetime of a cursor will help you. You should not always think that cursors will improve performance; sometimes it would be more efficient to use set-based processing rather than cursors, so understand the requirement and use the appropriate method.