Re: PL/pgSQL cursors should get generated portal names by default

st 2. 11. 2022 v 0:39 odesílatel Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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

Regards

Pavel

> regards, tom lane
>
> [1]
> https://www.postgresql.org/message-id/166689990972.627.16269382598283029015%40wrigleys.postgresql.org
>
>