Hi
The lifecycle of cursors in plpgsql is not strictly joined with the life cycle of related cursor's variables. Without breaking compatibility it is not possible to change this behaviour. Usually it doesn't cause problems, but in some cases very big numbers or unclosed cursors can force memory issues that are not simple to investigate and are not too simple (in a bigger project) to fix.
I think we can reduce this issue by enhancing the syntax of the OPEN statement. New syntax can looks like
Current syntax (still will be supported)
OPEN cursorvar ...
New syntax
OPEN LOCAL cursorvar ...
With the clause LOCAL the opened cursor (and related portal) will be surely closed immediately after function exit.
Probably we can enhance the syntax of DECLARE section too, so should be possible to write
DECLARE cursorvar LOCAL CURSOR ...
What do you think about this proposal?
Regards
Pavel