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