Refcursor in pl/pgsql

Question

I have a function written in pl/pgsql which works in the same way as the one described below:

CREATE FUNCTION reffunc(text) RETURNS refcursor AS '
BEGIN
    OPEN $1 FOR SELECT col FROM test WHERE c1=$1;
    RETURN $1;
END;
' LANGUAGE plpgsql;

I want to be able to use this with a single select command as opposed to the documented way which (using a transaction) is:

BEGIN;
SELECT reffunc('funccursor');
FETCH ALL IN "<unnamed cursor 1>";
COMMIT;

I am sure I have been able to do this before, however i can't remember how i have done it, or find it documented. Is this possible? Or is it possible to write this function in such a way that it can be outputted without using a refcursor.

I am expecting multiple rows to be returned and some data checking is done in the function before it is returned. Hence the necessity for the use of a stored procedure.

Answer 1

Sure it's possible to return without cursor.

Example:

CREATE FUNCTION reffunc(in_c1 text) RETURNS setof test AS '
DECLARE
    temprec test;
BEGIN
    FOR temprec in SELECT col FROM test WHERE c1 = in_c1 LOOP
        return next temprec;
    END LOOP;
    RETURN;
END;
' LANGUAGE plpgsql;
select * from reffunc('funccursor');

If you're using 8.4 you can also use "RETURN QUERY" which is even nicer.