Updated May 3, 2023
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.
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/pgsqlBelow 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');
\d+ employee;
\d+ customer;
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;
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;
fetch 2 from test_cur;
4. Close
Below is the syntax and example:
Syntax
Close cursor_name;Example
close test_cur;
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;
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.
