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

Wow. Thank you! Sorry, i should read the documentation more carefully

03.08.2016, 16:04, "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com>:
> 2016-08-03 11:53 GMT+02:00 <klimych(at)tut(dot)by>:
>> The following bug has been logged on the website:
>>
>> Bug reference:      14275
>> Logged by:          Oleg Klimovich
>> Email address:      klimych(at)tut(dot)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(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-bugs