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;
 id
----
  1
(1 row)
test=# FETCH 4 FROM mycur;
 id
----
  2
  3
  4
  5
(4 rows)
test=# COMMIT;
COMMIT

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