Summary: in this tutorial, we will show you how to use PL/pgSQL Cursor and give you some practical examples of using cursors.
A PL/pgSQL cursor allows us to encapsulate a query and process each individual row at a time. We use cursors when we want to divide a large result set into parts and process each part individually. If we process it at once, we may have a memory overflow error.
In addition, we can develop a function that returns a reference to a cursor. This is an efficient 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:
PL/pgSQL Cursor
- 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.
Declaring cursors
To access to a cursor, you need to declare a cursor variable at the declaration section of a block. PostgreSQL provides us with a special type called REFCURSOR to declare a cursor variable.
DECLARE
my_cursor REFCURSOR;
Another way to declare a cursor that bounds to a query is using the following syntax:
cursor_name [ [NO] SCROLL ] CURSOR [( name datatype, name data type, ...)] FOR query;
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:
DECLARE
cur_films CURSOR FOR SELECT * FROM film;
cur_films2 CURSOR (year integer) FOR SELECT * FROM film WHERE release_year = year;
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 syntax for opening an unbound and bound cursors.Opening unbound cursors
We open an unbound cursor using the following syntax:OPEN unbound_cursor_variable [ [ NO ] SCROLL ] FOR query;
Because 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:
OPEN my_cursor FOR SELECT * FROM city WHERE counter = p_country;
PostgreSQL allows us to open a cursor and bound it to a dynamic query. Here is the syntax:
OPEN unbound_cursor_variable[ [ NO ] SCROLL ]
FOR EXECUTE query_string [USING expression [, ... ] ];
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.
query := 'SELECT * FROM city ORDER BY $1';
OPEN cur_city FOR EXECUTE query USING sort_field;
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.OPEN cursor_variable[ (name:=value,name:=value,...)];
In the following example, we open bound cursors cur_films and cur_films2 that we declared above:
OPEN cur_films;
OPEN cur_films2(year:=2005);
Using cursors
After opening a cursor, we can manipulate it using FETCH, MOVE, UPDATE, or DELETE statement.
Fetching the next row
FETCH [ direction { FROM | IN } ] cursor_variable INTO target_variable;The FETCH statement gets the next row from the cursor and assign 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 the valid for the cursor:
- NEXT
- LAST
- PRIOR
- FIRST
- ABSOLUTE count
- RELATIVE count
- FORWARD
- BACKWARD
See the following examples of fetching cursors.
FETCH cur_films INTO row_film;
FETCH LAST FROM row_film INTO title, release_year;
Moving the cursor
MOVE [ direction { FROM | IN } ] cursor_variable;
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.
MOVE cur_films2;
MOVE LAST FROM cur_films;
MOVE RELATIVE -1 FROM cur_films;
MOVE FORWARD 3 FROM cur_films;
Deleting or updating 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:UPDATE table_name
SET column = value, ...
WHERE CURRENT OF cursor_variable;
DELETE FROM table_name
WHERE CURRENT OF cursor_variable;
See the following example.
UPDATE film SET release_year = p_year
WHERE CURRENT OF cur_films;
Closing cursors
To close an opening cursor, we use CLOSE statement as follows:CLOSE cursor_variable;
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.CREATE OR REPLACE FUNCTION get_film_titles(p_year INTEGER)
RETURNS text AS $$
DECLARE
titles TEXT DEFAULT '';
rec_film RECORD;
cur_films CURSOR(p_year INTEGER)
FOR SELECT
FROM film
WHERE release_year = p_year;
BEGIN
-- Open the cursor
OPEN cur_films(p_year);
LOOP
-- fetch row into the film
FETCH cur_films INTO rec_film;
-- exit when no more row to fetch
EXIT WHEN NOT FOUND;
-- build the output
IF rec_film.title LIKE '%ful%' THEN
titles := titles || ',' || rec_film.title || ':' || rec_film.release_year;
END IF;
END LOOP;
-- Close the cursor
CLOSE cur_films;
RETURN titles;
END; $$
LANGUAGE plpgsql;
SELECT get_film_titles(2006);
,Grosse Wonderful:2006,Day Unfaithful:2006,Reap Unfaithful:2006,Unfaithful Kill:2006,Wonderful Drop:2006
In this tutorial, we have shown you how to work with PL/pgSQL cursor to loop through a set of rows and process each row individually.
No comments:
Post a Comment