st 2. 11. 2022 v 0:39 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:
There's a complaint at [1] about how you can't re-use the same cursor variable name within a routine called from another routine that's already using that name. The complaint is itself a bit under-documented, but I believe it is referring to this ancient bit of behavior:
A bound cursor variable is initialized to the string value representing its name, so that the portal name is the same as the cursor variable name, unless the programmer overrides it by assignment before opening the cursor.
So if you try to nest usage of two bound cursor variables of the same name, it blows up on the portal-name conflict. But it'll work fine if you use unbound cursors (i.e., plain "refcursor" variables):
But an unbound cursor variable defaults to the null value initially, so it will receive an automatically-generated unique name, unless overridden.
I wonder why we did it like that; maybe it's to be bug-compatible with some Oracle PL/SQL behavior or other? Anyway, this seems non-orthogonal and contrary to all principles of structured programming. We don't even offer an example of the sort of usage that would benefit from it, ie that calling code could "just know" what the portal name is.
I propose that we should drop this auto initialization and let all refcursor variables start out null, so that they'll get unique portal names unless you take explicit steps to do something else. As attached.
+1
(Obviously this would be a HEAD-only fix, but maybe there's scope for improving the back-branch docs along lines similar to these changes.)
+1
I agree with this proposal. The current behavior breaks the nesting concept.
Unfortunately, it can breaks back compatibility, but I think so I am possible to detect phony usage of cursor's variables in plpgsql_check
