function in pgAdmin

From: rwade(at)uci(dot)edu
To: pgsql-general(at)postgresql(dot)org
Subject: function in pgAdmin
Date: 2009-05-02 01:54:59
Message-ID: 8515426deaea8d2477c2cd5ceb3aeb7e.squirrel@webmail.uci.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Craig Ringer 2009-05-02 02:50:09 Re: recover corrupt DB?
Previous Message Adam B 2009-05-01 22:49:49 Re: Possible to prevent transaction abort?