Re: BUG #14275: cursor's variable in pgsql doesn't respect scope

2016-08-03 11:53 GMT+02:00 <klimych@tut.by>:

> The following bug has been logged on the website:
>
> Bug reference:      14275
> Logged by:          Oleg Klimovich
> Email address:      klimych@tut.by
> PostgreSQL version: 9.5.3
> Operating system:   Windows 7, Windows 8, Ubuntu 14.04
> Description:
>
> DO $$
> DECLARE
>  cur cursor for select 1; -- (1)
> BEGIN
>   open cur; -- (2)
>
>   DECLARE
>     cur cursor for select 2; -- (3)
>   BEGIN
>     open cur; -- (4)
>     close cur; -- (5)
>   END;
>
>   close cur; -- (6)
> end $$;
>
> Executing of the code gives error "cursor "cur" already in use". Evedently,
> PG in statement (4) refers to the variable, defined in statement (1). (and
> I
> expect it should be variable, defined in statement (3)).
> Futhermore, same error exists even across different functions:
>
> create function func1() returns void as $$
> declare
>   cur cursor for select 1;
> BEGIN
>   open cur;
>   close cur;
> end
> $$
>  LANGUAGE 'plpgsql';
>
> create function func2() returns void as $$
> declare
>   cur cursor for select 1;
> BEGIN
>   open cur;
>
>   PERFORM func1();
>
>   close cur;
> end
> $$
>  LANGUAGE 'plpgsql';
>
> select func2();
>
> So, cursor's variable is kind of global. I just hope it's a bug and not
> "feature" (at least I haven't found mention of such behaviour in
> documentation)
>

It is feature - PLpgSQL engine uses named cursor accessed via SPI API. The
name of SPI cursor is generated by cursor variable name. SPI API has zero
relation to plpgsql block structure.

See source code pl_exec.c exec_stmt_open

Regards

Pavel


>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>