Re: passing cursors from one PL function to another

El día 26 de agosto de 2011 09:15, Merlin Moncure <mmoncure(at)gmail(dot)com> escribió:
> 2011/8/26 Martín Marqués <martin(dot)marques(at)gmail(dot)com>:
>> El día 26 de agosto de 2011 00:04, Merlin Moncure <mmoncure(at)gmail(dot)com> escribió:
>>> 2011/8/25 Martín Marqués <martin(dot)marques(at)gmail(dot)com>:
>>>> CREATE OR REPLACE FUNCTION prueba_cursor(codigo integer, curCursor refcursor)
>>>>   RETURNS SETOF refcursor AS
>>>> $BODY$
>>>> DECLARE
>>>>       cur alias for $2;
>>>> BEGIN
>>>>       PERFORM mpf.ConstruyeCursorDesdeQuery('cur' ,'SELECT * from
>>>> tab1 WHERE field < 11000');
>>>> END;
>>>> $BODY$
>>>> LANGUAGE 'plpgsql' VOLATILE
>>>> COST 100
>>>> ROWS 1000;>
>>>> CREATE OR REPLACE FUNCTION construyecursordesdequery(refcursor, query text)
>>>>   RETURNS SETOF refcursor AS
>>>> $BODY$
>>>> BEGIN
>>>>   OPEN $1 FOR Select * from tab1 where field < 11000;
>>>>   RAISE NOTICE '%', $1;
>>>>   RETURN NEXT $1;
>>>> END;
>>>> $BODY$
>>>> LANGUAGE 'plpgsql' VOLATILE
>>>> COST 100
>>>> ROWS 1000;
>>>>
>>>> begin;
>>>> select * from prueba_cursor4(1, 'h');
>>>> end;
>>>
>>> you pretty much had it.
>>>> select * from prueba_cursor4(1, 'h');
>>> should be
>>>> select * from prueba_cursor(1, 'h');
>>>
>>> after that, but inside the transaction, you can just do:
>>> fetch all from 'cur';
>>
>> That was a typo related with copy & paste. Sorry.
>>
>>> note neither of your functions need to return setof fwict.  you are
>>> returning one cursor, not a set of them.
>>
>> That's because originally I was trying to get more then one cursor.
>>
>> Anyway, I was getting an annoying error on a windows server, and now
>> that I test it on my Linux installation it works like a charm.
>>
>> Could it be that I was making changes to the functions and not
>> dropping them before recreating?
>
> not likely -- got the error text?.

The error is version related. On 8.4, it works great. But with 8.3
(which is the version being used in production) I get this:

# select * from prueba_cursor(1, 'a');
ERROR: se llamó una función que retorna un conjunto en un contexto
que no puede aceptarlo
CONTEXTO: PL/pgSQL function "construyecursordesdequery" line 3 at RETURN NEXT
sentencia SQL: «SELECT construyeCursorDesdeQuery( $1 ,'SELECT * from
tab1 WHERE field < 11000')»
PL/pgSQL function "prueba_cursor" line 3 at PERFORM

Why does it work on 8.4 and not on 8.3? Any work around that doesn't
involve upgradeing the DB server?

--
Martín Marqués
select 'martin.marques' || '@' || 'gmail.com'
DBA, Programador, Administrador