EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials PostgreSQL Tutorial Cursors in PostgreSQL
 

Cursors in PostgreSQL

Priya Pedamkar
Article byPriya Pedamkar

Updated May 3, 2023

Cursors in PostgreSQL

 

 

Introduction to cursors in PostgreSQL

A cursor is very important in PostgreSQL, using a Cursors in PostgreSQL, it is possible to encapsulate the query instead of executing a whole query at once; after encapsulating query, it is possible to read a few rows from the result set, the main purpose of doing this is to avoid memory consumption of database server if the result of the query contains more rows, for-loop automatically use the cursor to avoid memory issue in PostgreSQL, this is automatically done by for loop in PostgreSQL, a most important use of cursor is it will read the result in few rows at one time.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Syntax 

Below is the syntax.

Create or replace function (function name)
Return text (return value) as $$
DECLARE cursor_name (Any name given to cursor) [BINARY] [ INSENSITIVE ] [ SCROLL ]
CURSOR (keyword) for query (query to use in cursor)
[ for { READ ONLY (defines cursor only for read only) | UPDATE [ OF column ]}]
Begin
Open cursor cursor_name
FETCH (KEYWORD) [ FORWARD | BACKWARD ] (specify the direction)
[ # | ALL | NEXT | PRIOR (Specify the direction) ]
[ IN | FROM ]
CURSOR (cursor name)
Close cursor_name
Return
END $$
Language pl/pgsql

Below is the parameter description of the above syntax as follows.

  • Declare – Declare keyword to declare a cursor in PostgreSQL.
  • Cursor name – Any name given to the cursor to declare a cursor.
  • Binary – This is an optional cursor it fetches output in ASCII format. It is only efficient for custom applications.
  • Insensitive – Keyword that describes the default behavior. The insensitive keyword defines that data was never changed from other connections in the PostgreSQL cursor.
  • Scroll – The scroll keyword also describes the default behavior of PostgreSQL. The scroll is defined as the cursor that can select multiple rows at one time.
  • The cursor for – It is used to describe the complete query result set.
  • Query – Actual query used in declare cursor to retrieve the result set data.
  • Read-only – Read the only keyword is defined as the cursor is only for read-only.
  • Fetch – Use the fetch command to retrieve rows.
  • Forward – Specify the direction; in PostgreSQL, forward is the default.
  • Backwards – Specify the direction.
  • Next – The next keyword returns the next cursor row from the current cursor position.
  • Prior – This keyword causes a single row preceding the current cursor position.
  • Create or replace function – Create a function to create a new function.
  • Declare – Variable to declare a cursor in PostgreSQL.
  • Begin – Begin keyword.
  • Open – Open the keyword to open the cursor.
  • Close – Close the keyword to close the cursor.
  • Language – Language used to create a function to describe the cursor in PostgreSQL.

Cursors actions in PostgreSQL

We are using employee and customer tables to describe examples.

Code:

CREATE TABLE Employee ( emp_id INT NOT NULL, emp_name character(10) NOT NULL, emp_address character(20) NOT NULL, emp_phone character(14), PRIMARY KEY (emp_name));
INSERT INTO Employee (emp_id, emp_name, emp_address, emp_phone) VALUES (1, 'ABC', 'Pune', '1234567890');
INSERT INTO Employee (emp_id, emp_name, emp_address, emp_phone) VALUES (1, 'PQR', 'Pune', '1234567890');
INSERT INTO Employee (emp_id, emp_name, emp_address, emp_phone) VALUES (1, 'XYZ', 'Mumbai', '1234567890');

Cursors in PostgreSQL output 1

\d+ employee;

Cursors in PostgreSQL output 2

\d+ customer;

Cursors in PostgreSQL output 2.2

Example of employee and customer table to describe cursor

Below is the action of the cursor as follows.

  • Declare
  • Open
  • Fetch
  • Close

1. Declare

Below is the syntax for declaring a cursor in PostgreSQL. In the below example, test_cur is declared to hold all records from the employee table.

Syntax 

Below is an example of declaring:

DECLARE cursor_name (Any name given to cursor) [BINARY] [INSENSITIVE] [SCROLL]
CURSOR (keyword) for query (query to use in cursor)
[For {READ ONLY | UPDATE [OF column (defines cursor only for read only)]}]

Example 

Below is an example of declaring a cursor in PostgreSQL.

BEGIN;
DECLARE test_cur CURSOR WITH HOLD FOR SELECT * FROM Employee ORDER BY emp_id;
COMMIT;

output 3

2. Open

In PostgreSQL, you use the OPEN keyword to open a cursor. Below is the example

Syntax 

Open [[ NO ] SCROLL } FOR query (any query);

Example 

Open test_cursor for select * from employee where emp_id = 1;

3. Fetch

Below is the syntax and example:

Syntax

FETCH [direction {FROM | IN}] cursor_name into target;

Example 

fetch 1 from test_cur;

Cursors in PostgreSQL output 4

fetch 2 from test_cur;

Cursors in PostgreSQL output 4.2

4. Close

Below is the syntax and example:

Syntax 

Close cursor_name;

Example 

close test_cur;

output 5

Close the cursor.

Example of the cursor in PostgreSQL

  • Below is an example of creating a cursor using the function in PostgreSQL.
  • We have used the employee and customer tables to join the table.

Example 

CREATE OR REPLACE FUNCTION testing_trigger()
RETURNS SETOF varchar AS
$cursor_test$
DECLARE
cur CURSOR FOR select * from employee emp
left join customer cust on emp.emp_id = cust.cust_id;
test_cur RECORD;
BEGIN
open cur;
LOOP
fetch cur into test_cur;
exit when test_cur = null;
if test_cur.customer like '%AB%' then
return next test_cur.customer;
end if;
END LOOP;
close cur;
END;
$cursor_test$
LANGUAGE plpgsql VOLATILE;

output 6

Importance of cursors in PostgreSQL

Below is the importance as follows.

  • The main importance of the cursor in PostgreSQL retrieves a single record from the result set.
  • This cursor will save time because we don’t need to wait to retrieve or process the whole data set.
  • This cursor saves memory because the client and server do not need more memory to store the data results.
  • The cursor minimizes the load on the server as well as our network bandwidth.
  • If we are doing operations on the table, it will not affect the cursor directly. While holding a row, other processes can update, select or delete the rows.

Conclusion

It is essential in PostgreSQL to fetch single records from the complete result set. It is used to save memory and network bandwidth on the server. If we read data from the cursor, other sessions can do their operations without impacting other connections.

Recommended Articles

We hope that this EDUCBA information on “Cursors in PostgreSQL” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. What is PostgreSQL?
  2. PostgreSQL String Functions
  3. PostgreSQL Operators
  4. How to Modify PostgreSQL Views?

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW