Re: Dynamic SQL in Function

On Thu, Apr 23, 2009 at 11:36 AM, <rwade(at)uci(dot)edu> wrote:
>> On Wed, Apr 22, 2009 at 12:29 PM,  <rwade(at)uci(dot)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