Introduction to cursors in PostgreSQL
A cursor is very important in PostgreSQL, using a cursor 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 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, most important use of cursor is it will read result 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/pgsql
Below is the parameter description of the above syntax are 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 behaviour. Insensitive keyword defines that data was never changed from other connections in PostgreSQL cursor.
- Scroll – Scroll keyword also describes the default behaviour of PostgreSQL. The scroll is defined as that cursor 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 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 used as 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 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 are as follows.
- Declare
- Open
- Fetch
- Close
1. Declare
Below is the syntax of declare cursor in PostgreSQL. In the below example, test_cur is declared to hold all records from the employee table.
Syntax
Below is the 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 the example of declaring cursor in PostgreSQL.
BEGIN;
DECLARE test_cur CURSOR WITH HOLD FOR SELECT * FROM Employee ORDER BY emp_id;
COMMIT;
2. Open
The open keyword is used to open a cursor in PostgreSQL. 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 the example of creating a cursor using the function in PostgreSQL.
- We have used the employee and customer table 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 are 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 for retrieving or processing the whole set of data.
- This cursor saves memory because the client and server did 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 we are holding a row, other processes can update, select or delete the rows.
Conclusion
It is essential in PostgreSQL to fetch single records from the whole result set. It is used to saves memory and network bandwidth on the server.
If we are reading data from the cursor, other sessions can do their operations; there is no impact on other connections.
Recommended Articles
This is a guide to Cursors in PostgreSQL. Here we discuss the Cursor’s actions along with the examples and importance. You may also have a look at the following articles to learn more –
