Re: passing cursors from one PL function to another

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?

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