In plpgsql, don't preassign portal names to bound cursor variables.
A refcursor variable that is bound to a specific query (by declaring
it with "CURSOR FOR") now chooses a portal name in the same way as an
unbound, plain refcursor variable. Its string value starts out as
NULL, and unless that's overridden by manual assignment, it will be
replaced by a unique-within-session portal name during OPEN.
The previous behavior was to initialize such variables to contain
their own name, resulting in that also being the portal name unless
the user overwrote it before OPEN. The trouble with this is that
it causes failures due to conflicting portal names if the same
cursor variable name is used in different functions. It is pretty
non-orthogonal to have bound and unbound refcursor variables behave
differently on this point, too, so let's change it.
This change can cause compatibility problems for applications that
open a bound cursor in a plpgsql function and then use it in the
calling code without explicitly passing back the refcursor value
(portal name). If the calling code simply assumes that the portal
name matches the called function's variable name, it will now fail.
That can be fixed by explicitly assigning a string value to the
refcursor variable before OPEN, e.g.
DECLARE myc CURSOR FOR SELECT ...;
BEGIN
myc := 'myc'; -- add this
OPEN myc;
We have no documentation examples showing the troublesome usage
pattern, so we can hope it's rare in practice.
Patch by me; thanks to Pavel Stehule and Jan Wieck for review.
Discussion: https://postgr.es/m/1465101.1667345983@sss.pgh.pa.us
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/d747dc85aec536c471fd7c739695e155627b08fd
Modified Files
--------------
doc/src/sgml/plpgsql.sgml | 42 ++++++++++++++++++++++++++---------
doc/src/sgml/ref/declare.sgml | 16 +++++++++++++
src/pl/plpgsql/src/pl_gram.y | 27 ----------------------
src/test/regress/expected/plpgsql.out | 19 ++++++++++++++++
src/test/regress/sql/plpgsql.sql | 20 +++++++++++++++++
5 files changed, 87 insertions(+), 37 deletions(-)