1. Postgresql Cursor Example Two
2. Postgresql Cursor Loop Example

In this syntax: The queryexpression is an SQL statement.; The using clause is used to pass parameters to the query.; The following block shows how to use the for loop statement to loop through a dynamic query. It has two configuration variables: sorttype: 1 to sort the films by title, 2 to sort the films by release year.; reccount: is the number of rows to query from the film table. Michael Fuhr wrote: Right - when you open a cursor PostgreSQL doesn't know how many rows it will return. PostgreSQL selects a query plan based on an.estimate. of how many rows the query will return, but until you fetch all the rows you can't know for sure how many rows there will be. So if i make a but data set as result of a cursor I only 'pay' for the rows I actually fetch?

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:

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:

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:

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:

The result will look something like this:

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:

Moving a cursor example

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

Postgres will return the following response: MOVE 1.

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

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:

Closing a cursor example

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

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.

Introduction to Cursors in PostgreSQL

If you’re writing code that interacts with PostgreSQL using Python or PHP, you’ll probably want to use cursors in your scripts. A PostgreSQL database cursor is a read-only pointer that allows a program, regardless of the language used, to access the result set of a query. This conserves the free memory of the server or machine running the SQL commands when a result set contains a large number of rows. Using cursors to iterate over rows of data also offers more control over the SQL records returned and makes the whole process more efficient. In this article, we’ll take a closer look at cursors and show you how to use a PostgreSQL cursor in Python, PHP and psql.

Cursors in Python using the PostgreSQL psycopg2 adapter

In Python, you can use the psycopg2 adapter for PostgreSQL to create cursors. A cursor can be created by calling the connection object’s cursor() method. Let’s look at an example where we instantiate a cursor and get its attributes:

NOTE: If you’re following along with this code example on your own machine, don’t forget to change the host parameters for the connect() method to match your PostgreSQL database’s settings.

When you pass the cursor instance to Python’s dir() function, it will return the following list of attributes:

You can get the cursor object’s type by passing it to Python’s type() function; it should return the following:

Execute a SQL query by calling the psycopg2 cursor object’s execute() method

The following block of Python code uses the cursor object’s execute() method to execute SQL commands to a PostgreSQL database:

The code shown above uses the cursor object’s fetchall() method to return all of the table’s records in the form of a Python list.

Instantiate Cursors in PHP using the PDO() method

Now that we’ve looked at a Python example, let’s check out some PHP code. Here’s how to create a cursor pointer in PHP using the PDO (PHP Data Objects) interface to connect to PostgreSQL:

Create a new PDO connection to PostgreSQL in PHP

The following PHP code passes the cursor object to the print_r() method to print the SQL commands. After that, it creates a new connection to PostgreSQL using the PDO() method:

Use the PHP prepare() method to avoid SQL injection attacks

When you’re writing PHP code that interacts with a database, it’s important to add safeguards against SQL injection attacks. In the following block of code, we call the connection object’s beginTransaction() method to disable auto-commit. This ensures that changes to a table or database will not be in effect until the PDO commit() method is called:

NOTE: The connection’s prepare() method, which returns a statement object, is used to help prevent SQL injection attacks.

The following code prints some class attributes for the PDO statement object:

Prepare an inner statement for the SQL query and execute

In the next code snippet, we prepare a SQL query and execute the PDO statement. This enables the inner statement to prepare a response from the cursor:

Postgresql Cursor Example Two

Execute the PDO inner statement and fetch records from a PostgreSQL table

Next, let’s FETCH all of the records from the cursor object by passing a SQL statement to the connection object’s prepare() method. This will return a PDO inner statement object:

The code shown above executes the inner statement and prints the response. To test out this example on your own machine, simply save the PHP code in a script with the .php file extension, making sure the file is in your web root directory. Then, navigate to the file in a browser tab.

Create a CURSOR inside of the ‘psql’ interface for PostgreSQL

It’s also possible to create and use a PostgreSQL cursor in the psql interface. You can enter into the psql command-line interface for PostgreSQL by typing your username, domain host (-h), and PostgreSQL database (-d):

Once you’re connected, you can use the DECLARE keyword to create a new cursor for a table in the database:

DECLARE CURSOR can only be used in transaction blocks

If you don’t use the keyword WITH HOLD after declaring your cursor, you’ll get an error saying: DECLARE CURSOR can only be used in transaction blocks. This is because a cursor no longer exists after a transaction is called. It will be freed from the system’s memory unless you explicitly tell it not to.

To avoid this issue, simply use the WITH HOLD keywords before the SQL command:

After you execute this statement, you’ll get a response from PostgreSQL saying:

Conclusion

Postgresql Cursor Loop Example

When you’re writing code that connects to PostgreSQL and executes queries, you want your interaction with the database to be as efficient as possible. Using cursors in your code can help you achieve this, allowing you to process queries with large result sets without maxing out your server’s free memory. In this article, we showed you how to create and use a PostgreSQL cursor in PHP, Python and psql. With all of these examples to guide you, you’ll be able to implement cursors in any of your database applications.