Re: passing cursors from one PL function to another

Actually, what we are trying to do is return 2 recordsets with the
same function call (simulate SP from SQL Server returning 2
recordsets).

I found out that I had to do PERFORM * FROM construyecursordesdequery($1, query)
which works now, but can't run 2 different queries on the same cursor.

I was able to do it on 8.4, but not on 8.3.

El día 29 de agosto de 2011 13:48, Martín Marqués
<martin(dot)marques(at)gmail(dot)com> escribió:
> 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
>

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