Cursors in PL/pgSQL in PostgreSQL Database System

PL/pgSQL is a loadable procedural language for the PostgreSQL database system. This procedural language can be used to create functions and trigger procedures to process large result sets. In PL/pgSQL, cursors are widely used to return set of records from functions. In this blog we will elaborate on the cursors used in PL/pgSQL for sequential processing of the record set and will cover following topics.

1.     Introduction of PL/PGSQL cursors

2.     Declare the cursor

3.     SCROLL CURSOR

4.     WITH HOLD cursor

5.     Open the cursor

6.     Fetch the result from the cursor into the variable

7.     Close the cursor

8.     How to use a cursor in PL/pgSQL function?

9.     Advantages of Cursors

Note - In this blog I am using a sample data set created using generate _series, a set returning function of PostgreSQL. 

generate_series() allows us to generate a set of data starting at some point, ending at another point, and optionally set the incrementing value.

1.     Introduction of PL/PGSQL cursors:

In PostgreSQL, cursor is a database object that allows you to traverse the result set of a query one row at a time. Cursors are useful when query results into a large number of record sets or when rows needed to process sequentially. When a query is ready for execution, PostgreSQL creates a portal from which the result rows can be fetched. During normal query execution, the whole result set is received in one step. In contrast, a cursor allows to fetch the result rows one by one. A cursor marks a position within a result set and is a single SQL statement.

2.     Declare the cursor:

Cursor positioned at the first record of the result after declaration.

Cursors can be accessed through cursor variable.

There are two ways to declare cursor variable:

• Using Unbound cursor

• Using cursor declaration syntax

Using Unbound cursor:

In this type, cursor is declared as a variable of type of refcursor. This type of cursor can be used with any query as it is not bounded with any query while declaring it. This type of cursor is known as Unbound cursor.

       Syntax: Declare cursor_name refcursor;

Here refcursor is a keyword. Unbound cursor is also known as a reference cursor.

Using cursor declaration syntax:

        Syntax:

            name [ [ NO ] SCROLL  ] CURSOR [ ( argument ) ] FOR query;

Here are two different examples to declare this type of cursor.

Example 1 - Bounded with fully specified query:

Example 2 - Parameterized query bounded cursors:

This cursor needs to be declared in functions only. In this cursor, key will be replaced by an integer parameter value when the cursor is opened.

NOTE: Parameterized query bounded cursor should be declared in FUNCTIONS only.

3.     SCROLL CURSOR: 

The SCROLL option should be specified when defining a cursor that will be used to fetch backwards.  PostgreSQL will allow backward fetches without SCROLL, if the cursor's query plan is simple and not requires any extra overhead to support it.  However, it is not advisable not to use backward fetches from a cursor that has not been created with SCROLL.

Limitations:

• The SCROLL option cannot be used when the cursor's query uses FOR UPDATE/SHARE.

• It is recommended that use NO SCROLL with a query that involves volatile functions. The implementation of SCROLL assumes that re-reading the query's output will give consistent results, which a volatile function might not do.

NO SCROLL CURSOR:

If NO SCROLL is specified, then backward fetches are disallowed in any case.

4.     WITH HOLD cursor:

Key features of WITH HOLD cursor:

• The rows represented by a WITH HOLD CURSOR are copied into a temporary file or memory area so that they remain available for subsequent transactions. This process may take unusually long time to process COMMIT;

• A cursor created with WITH HOLD is closed when an explicit CLOSE command is issued on it, or the session ends.

• WITH HOLD may not be specified when the query includes FOR UPDATE or FOR SHARE.

• If WITH HOLD cursor not closed, then the result set hogs server resources until the end of the database session.

Query1:

Query2: 

Output of both queries is same: 

5.     Open the cursor: 

In the PL/pgSQL functions, after declaring a cursor, we need to open the cursor with OPEN statement. 

Syntax:    OPEN cursor_name;

6.     Fetch the result from the cursor into the variable:

In PL/pgSQL functions, after opening a cursor, we can fetch the rows from cursor using FETCH statement.

Ways to FETCH the rows: 

1	FETCH NEXT	Fetches the next row from the cursor.
2	FETCH PRIOR	Fetches the previous row from the cursor.
3	FETCH FIRST	Fetches the first row from the cursor.
4	FETCH LAST	Fetches the last row from the cursor.
5	FETCH ALL	Fetches all rows from the cursor.

 Syntax:  FETCH NEXT FROM cursor_name INTO variable_list; 

Variable_list is a comma-separated list of variables that store the values of fetched from the cursor. It also can be a record.

Processing rows:

 Rows from the cursor can be processed by using LOOP statement.

 LOOP   

    -- Fetch the next row

    FETCH NEXT FROM cursor_name INTO variable_list;

    -- exit if not found

    EXIT WHEN NOT FOUND;

   -- Process the fetched row

...

 END LOOP;

7.     Close the cursor: 

After processing the rows, cursor need to be closed.

Syntax: CLOSE cursor_name 

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

8.     How to use a cursor in PL/pgSQL?

To Call above created function :

Result :

9.     Advantages of Cursors:

• Cursors are particularly useful in procedural code on the client side or in the database, because looping through the query results is possible by using cursor.

• A cursor allows you to have more than one SQL statement running at the same time, which is normally not possible in a single database session.

• Rather than executing a whole query at once, it is possible to set up a cursor that encapsulates the query, and then read the query result a few rows at a time.

• One of the advantages for using cursor is to avoid memory overrun when the result contains a large number of rows.

• When reference cursor is used, a reference to a cursor that a function has created is returned, allowing the caller to read the rows. This provides an efficient way to return large row sets from functions.  

Final Thoughts:

 This is all about types of cursors and the steps required to use the cursor in PL/pgSQL functions. Please stay tuned, we will see cursor attributes and how to use reference cursor/unbound cursors in PL/pgSQL functions in my next blog.

Till then Happy Learning!