Re: passing cursors from one PL function to another - Mailing list pgsql-general

2011/8/29 Martín Marqués <martin.marques@gmail.com>:
> 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.
>

in your example you use a static cursor.

Pavel

> 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.marques@gmail.com> escribió:
>> El día 26 de agosto de 2011 09:15, Merlin Moncure <mmoncure@gmail.com> escribió:
>>> 2011/8/26 Martín Marqués <martin.marques@gmail.com>:
>>>> El día 26 de agosto de 2011 00:04, Merlin Moncure <mmoncure@gmail.com> escribió:
>>>>> 2011/8/25 Martín Marqués <martin.marques@gmail.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
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>