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;
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment