PostgreSQL Cursor

A PostgreSQL database cursor is a read-only pointer that allows a program, regardless of the language used, to access the result set of a query. This conserves the free memory of the server or machine running the SQL commands when a result set contains a large number of rows. Using cursors to iterate over rows of data also offers more control over the SQL records returned and makes the whole process more efficient.

1. First, declare a cursor.
2. Next, open the cursor.
3. Then, fetch rows from the result set into a target.
4. After that, check if there is more row left to fetch. If yes, go to step 3, otherwise, go to step 5.
5. Finally, close the cursor.

he following is valid for the cursor:

• NEXT
• LAST
• PRIOR
• FIRST
• ABSOLUTE count
• RELATIVE count
• FORWARD
• BACKWARD

Using DECLARE CURSOR and FETCH

test=# BEGIN;
BEGIN
test=# DECLARE mycur CURSOR FOR
SELECT * FROM t_large WHERE id > 0;
DECLARE CURSOR
test=# FETCH NEXT FROM mycur;
test=# FETCH 4 FROM mycur;
test=# COMMIT;
COMMIT

Input:

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 title, release_year
FROM film
WHERE release_year = p_year;
BEGIN
OPEN cur_films(p_year);
LOOP
FETCH cur_films INTO rec_film;
EXIT WHEN NOT FOUND;
IF rec_film.title LIKE '%ful%' THEN
titles := titles || ',' || rec_film.title || ':' || rec_film.release_year;
END IF;
END LOOP;
CLOSE cur_films;
RETURN titles;
END; $$
LANGUAGE plpgsql;

output:

SELECT get_film_titles(2006);

Grosse Wonderful:2006,Day Unfaithful:2006,Reap Unfaithful:2006,Unfaithful Kill:2006,Wonderful Drop:2006