How do I view the result set of a function that returns a refcursor in
pgAdmin?
I am trying to test it in pgadmin my calling it like this, but I can't see
the result set, it only says:
Query result with 1 rows discarded.
Query result with 328 rows discarded.
Query returned successfully with no result in 32 ms.
How I'm calling in pgAdmin:
begin;
select select_movies_by_web_search('Test', 2008, '', null, null, null);
fetch all in moviecursor;
commit;
Here is my function:
CREATE OR REPLACE FUNCTION select_movies_by_web_search(title character
varying(100),
movieyear integer, director_rest_of_name character varying(50),
director_last_name character varying(50), star_first_name character
varying(50),
star_last_name character varying(50))
RETURNS refcursor AS
$BODY$
DECLARE
rc refcursor default 'moviecursor';
sql character varying(2000);
BEGIN
sql = 'SELECT id, title, "year", director_rest_of_name,
director_last_name, banner_url, trailer_url
FROM movies';
open rc for EXECUTE sql;
RETURN rc;
END;$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION select_movies_by_web_search(character varying, integer,
character varying, character varying, character varying,
character varying) OWNER TO testuser;
Thanks
Ryan
