PL/pgSQL Cursor

Summary: in this tutorial, you will learn about the PL/pgSQL Cursors and how to use them to process a result set, row by row.

A PL/pgSQL cursor allows you to encapsulate a query and process each individual row at a time.

Typically, you use cursors when you want to divide a large result set into parts and process each part individually. If you process it at once, you may have a memory overflow error.

On top of that, you can develop a function that returns a reference to a cursor. This is an effective way to return a large result set from a function. The caller of the function can process the result set based on the cursor reference.

The following diagram illustrates how to use a cursor in PostgreSQL:

• First, declare a cursor.
• Next, open the cursor.
• Then, fetch rows from the result set into a target.
• After that, check if there is more row left to fetch. If yes, go to step 3, otherwise, go to step 5.
• Finally, close the cursor.

We will examine each step in more detail in the following sections.

Declaring cursors

To access to a cursor, you need to declare a cursor variable in the declaration section of a block. PostgreSQL provides you with a special type called REFCURSOR to declare a cursor variable.

You can also declare a cursor that bounds to a query by using the following syntax:

First, you specify a variable name for the cursor.

Next, you specify whether the cursor can be scrolled backward using the SCROLL . If you use NO SCROLL , the cursor cannot be scrolled backward.

Then, you put the CURSOR keyword followed by a list of comma-separated arguments ( name datatype ) that defines parameters for the query. These arguments will be substituted by values when the cursor is opened.

After that, you specify a query following the FOR keyword. You can use any valid SELECT statement here.

The following example illustrates how to declare cursors:

The cur_films is a cursor that encapsulates all rows in the film table.

The cur_films2 is a cursor that encapsulates film with a particular release year in the film table.

Opening cursors

Cursors must be opened before they can be used to query rows. PostgreSQL provides the syntax for opening an unbound and bound cursor.

Opening unbound cursors

You open an unbound cursor using the following syntax:

Because the unbound cursor variable is not bounded to any query when we declared it, we have to specify the query when we open it. See the following example:

PostgreSQL allows you to open a cursor and bound it to a dynamic query. Here is the syntax:

In the following example, we build a dynamic query that sorts rows based on a sort_field parameter and open the cursor that executes the dynamic query.

Opening bound cursors

Because a bound cursor already bounds to a query when we declared it, so when we open it, we just need to pass the arguments to the query if necessary.

In the following example, we open bound cursors cur_films and cur_films2 that we declared above:

Using cursors

After opening a cursor, we can manipulate it using FETCH , MOVE , UPDATE, or DELETE statement.

Fetching the next row

The FETCH statement gets the next row from the cursor and assigns it a target_variable , which could be a record, a row variable, or a comma-separated list of variables. If no more row found, the target_variable is set to NULL (s).

By default, a cursor gets the next row if you don’t specify the direction explicitly. The following is valid for the cursor:

• NEXT
• LAST
• PRIOR
• FIRST
• ABSOLUTE count
• RELATIVE count
• FORWARD
• BACKWARD

Note that FORWARD and BACKWARD directions are only for cursors declared with SCROLL option.

See the following examples of fetching cursors.

Moving the cursor

If you want to move the cursor only without retrieving any row, you use the MOVE statement. The direction accepts the same value as the FETCH statement.

Deleting or updating the row

Once a cursor is positioned, we can delete or update row identifying by the cursor using DELETE WHERE CURRENT OF or UPDATE WHERE CURRENT OF statement as follows:

See the following example.

Closing cursors

To close an opening cursor, we use CLOSE statement as follows:

The CLOSE statement releases resources or frees up cursor variable to allow it to be opened again using OPEN statement.

PL/pgSQL cursors – putting it all together

The following get_film_titles(integer) function accepts an argument that represents the release year of a film. Inside the function, we query all films whose release year equals to the released year passed to the function. We use the cursor to loop through the rows and concatenate the title and release year of film that has the title contains the ful word.

In this tutorial, you have learned how to work with PL/pgSQL cursor to loop through a set of rows and process each row individually.