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?
--
Martín Marqués
select 'martin.marques' || '@' || 'gmail.com'
DBA, Programador, Administrador
