Can't we use cursors inside pl/pgSQL functions. I tried defining the transaction with Begin work and end work statements, because the documentation says that cursors can only be defined in these blocks. But this does not work. I have the following code :
create function sample() returns text as '
declare
retValue text;
begin work
declare retCur Cursor for select * from <table name>;
Fetch 1 in retCur;
close retCur;
return ''abc'';
end work;'
language 'plpgsql';
When i execute the above it says ' parse error at or near "work" '. I know that in plpgsql function only begin and end keywords define the block, but since I need to retrieve the value one by one from the table I need to do this.
Is it possible to do the above? If yes, how can I return the value retrieved from the table, from the function?
Regards,
Atul