Postgres Cursor

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

When you’re working with data stored in PostgreSQL, there may be situations where you want to handle the data returned in your result set on a row-by-row basis. A database cursor can help you manage your results and process individual records as your traverse over the rows of a result set. In this article, we’ll take a closer look at PostgreSQL cursors and show you some simple examples of their use.

Prerequisite

Before moving forward with this tutorial, make sure you install and configure the following:

It’s also important to have some basic knowledge of PostgreSQL to be able to follow along with the tutorial.

What is a PostgreSQL Cursor?

A cursor is a pointer with read-only access to the results of a SELECT statement. Applications use cursors to maintain a persistent connection to a Postgres database. Using a cursor helps an application to be more efficient, allowing it to retrieve different rows from a result set at different times without having to execute the query over and over adding various clauses like OFFSET and LIMIT.

There are four key commands associated with PostgreSQL cursors: DECLARE, FETCH, MOVE and CLOSE.

  • DECLARE – This command acts as the entry point for the cursor, where the cursor is created and executed. It defines the cursor in memory before populating it with information about the query’s returned result set.
  • FETCH – This command allows us to retrieve rows from an open cursor.
  • MOVE – As the name suggests, this command moves the current position of the cursor as specified within the returned result set.
  • CLOSE – This command closes the cursor and frees up any memory that was used during the process.

Declare a PostgreSQL Cursor

In this section, we’ll show how to declare a cursor in PostgreSQL. The basic syntax can be seen below:

1
2
3
4
DECLARE
cursorname
[ BINARY ] [ INSENSITIVE ] [ SCROLL ]
CURSOR FOR query

Let’s discuss the above syntax in more detail:

  • cursorname – This represents the name of the cursor.
  • [ BINARY ] – This optional keyword formats the retrieved output in a binary format.
  • [ INSENSITIVE ] [ SCROLL ] – These keywords are enabled by default in Postgres. The INSENSITIVE keyword ensures that data retrieved from a cursor isn’t changed by other connections or cursors. The SCROLL keyword lets Postgres know that a cursor can select multiple rows at a given time from a cursor.
  • query – This represents the query that returns the result set and makes it accessible to the cursor.

Declaring a Cursor Example

The following example shows how to declare a cursor in Postgres:

1
2
3
4
product=# BEGIN;
BEGIN
product=# DECLARE all_stock CURSOR FOR SELECT * FROM stock;
DECLARE CURSOR

In the syntax shown above, we begin a transaction block using the keyword BEGIN. After that, we open a cursor named all_stock with the query SELECT * FROM stock as its executed statement.

Fetching from Cursor

Shown below is the syntax for the FETCH command:

1
2
3
4
FETCH [ FORWARD | BACKWARD | RELATIVE ]
[ # | ALL | NEXT | PRIOR ]
{ IN | FROM }
cursor

In this syntax, we can use keywords like FORWARD and BACKWARD to control the direction of the cursor’s movement. The default value is FORWARD.

Fetching from cursor example

The following syntax is used to fetch rows from a cursor:

1
FETCH 3 FROM all_stock;

The result will look something like this:

1
2
3
4
5
6
7
product=# FETCH 3 FROM all_stock;
id | category_id | product_name | sku | description | price | quantity
----+-------------+-----------------+-------+-------------------------+-------+----------
1 | M01 | Monitor 22inch | HW020 | Computer monitor 22inch | 300 | 100
2 | M02 | Thermal Paste | HW040 | CPU thermal paste | 2 | 130
3 | M03 | Crimping Tool | HW021 | Networking tool | 14 | 10
(3 rows)

Moving a cursor

Since a cursor maintains its current position within a given result set, we can move the cursor to a new position using the MOVE command. We use the syntax shown below:

1
2
3
4
MOVE [ FORWARD | BACKWARD | RELATIVE ]
[ # | ALL | NEXT | PRIOR ]
{ IN | FROM }
cursor

Moving a cursor example

Let’s look at an example that shows how to move a cursor within a result set:

1
2
product=# MOVE FORWARD 1
product-# IN all_stock;

Postgres will return the following response: MOVE 1.

We can verify that this cursor operation was successful by executing another FETCH command:

1
2
3
4
5
product=# FETCH FROM all_stock;
id | category_id | product_name | sku | description | price | quantity
----+-------------+--------------+-------+-------------------+-------+----------
5 | M01 | SSD 250GB | HW033 | Solid State Drive | 24 | 60
(1 ROW)

Closing a cursor

Closing a cursor is a simple, but important, process. All you need to do is use the keyword CLOSE followed by the cursor name. The syntax is shown below:

1
CLOSE cursorname

Closing a cursor example

In our next example, we’ll close the all_stock cursor that we have been using:

1
CLOSE all_stock;

Postgres will return this: CLOSE CURSOR.

Then we perform a commit via the COMMIT command to the database to finish our transaction properly.

Conclusion

When you need to revisit a result set multiple times to access different rows of the result set, a cursor is the most effective tool for the job. In this article, we discussed Postgres cursors and explained how to use various commands to work with them. With these instructions to guide you, you’ll be able to make use of cursors in your own PostgreSQL database.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.