Обсуждение: Dynamic SQL in Function
If I have built a dynamic sql statement in a function, how do i return it as a ref cursor?
On Wed, Apr 22, 2009 at 12:29 PM, <rwade@uci.edu> wrote: > If I have built a dynamic sql statement in a function, how do i return it > as a ref cursor? CREATE FUNCTION reffunc(_ref) RETURNS refcursor AS $$ BEGIN OPEN _ref FOR execute 'SELECT * from foo'; RETURN _ref; END; $$ LANGUAGE plpgsql; BEGIN; SELECT reffunc('funccursor'); FETCH ALL IN funccursor; COMMIT;
n Wed, Apr 22, 2009 at 2:54 PM, Merlin Moncure <mmoncure@gmail.com> wrote: > On Wed, Apr 22, 2009 at 12:29 PM, <rwade@uci.edu> wrote: >> If I have built a dynamic sql statement in a function, how do i return it >> as a ref cursor? > > CREATE FUNCTION reffunc(_ref) RETURNS refcursor AS oops CREATE FUNCTION reffunc(_ref refcursor) RETURNS refcursor AS merlin
Is this possible without having to pass in the _ref parameter? Thanks Ryan > On Wed, Apr 22, 2009 at 12:29 PM, <rwade@uci.edu> wrote: >> If I have built a dynamic sql statement in a function, how do i return >> it >> as a ref cursor? > > CREATE FUNCTION reffunc(_ref) RETURNS refcursor AS > $$ > BEGIN > OPEN _ref FOR execute 'SELECT * from foo'; > RETURN _ref; > END; > $$ LANGUAGE plpgsql; > > BEGIN; > SELECT reffunc('funccursor'); > FETCH ALL IN funccursor; > COMMIT; > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > >
On Thu, Apr 23, 2009 at 11:36 AM, <rwade@uci.edu> wrote: >> On Wed, Apr 22, 2009 at 12:29 PM, <rwade@uci.edu> wrote: >>> If I have built a dynamic sql statement in a function, how do i return >>> it >>> as a ref cursor? >> >> CREATE FUNCTION reffunc(_ref) RETURNS refcursor AS >> $$ >> BEGIN >> OPEN _ref FOR execute 'SELECT * from foo'; >> RETURN _ref; >> END; >> $$ LANGUAGE plpgsql; >> >> BEGIN; >> SELECT reffunc('funccursor'); >> FETCH ALL IN funccursor; >> COMMIT; >> > Is this possible without having to pass in the _ref parameter? sure: CREATE FUNCTION reffunc() RETURNS refcursor AS $$ DECLARE _ref REFCURSOR default 'merlin'; BEGIN OPEN _ref FOR execute 'SELECT * from foo'; RETURN _ref; END; $$ LANGUAGE plpgsql; one thing I also forgot: refcursors are limited to transaction lifetime...make sure to wrap the function call with begin...end. merlin
_________________________________________________________________ Rediscover Hotmail®: Get e-mail storage that grows with you. http://windowslive.com/RediscoverHotmail?ocid=TXT_TAGLM_WL_HM_Rediscover_Storage2_042009
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