Thursday, 14 February 2019

postgres tutorial 12 cursor


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 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;

-------------------------------------
By default, a cursor gets the next row if you don’t specify the direction explicitly. The following is valid for the  cursor:

MOVE cur_films2;
MOVE LAST FROM cur_films;
MOVE RELATIVE -1 FROM cur_films;
MOVE FORWARD 3 FROM cur_films;

UPDATE table_name
SET column = value, ...
WHERE CURRENT OF cursor_variable;

DELETE FROM table_name
WHERE CURRENT OF cursor_variable;

No comments:

Post a Comment