Обсуждение: [BUGS] BUG #14463: refcursor cann't used with array or variadic parameter?
The following bug has been logged on the website:
Bug reference: 14463
Logged by: Zhou Digoal
Email address: digoal@126.com
PostgreSQL version: 9.6.1
Operating system: CentOS 6.x x64
Description:
postgres=# CREATE FUNCTION myfunc(variadic ref refcursor[]) RETURNS SETOF
refcursor AS $$
begin
open ref[1] for select * from pg_class;
return next ref[1];
open ref[2] for select * from pg_class;
return next ref[2];
end;
$$ lANGUAGE plpgsql;
ERROR: 42804: variable "$1" must be of type cursor or refcursor
LINE 3: open ref[1] for select * from pg_class;
^
LOCATION: plpgsql_yyparse, pl_gram.y:2187
Time: 0.854 ms
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #14463: refcursor cann't used with array or variadic parameter?
От
Pavel Stehule
Дата:
Hi
It is not a bug - it is feature. PLpgSQL statements doesn't expect a expression on some places.
2016-12-13 16:25 GMT+01:00 <digoal@126.com>:
CREATE FUNCTION myfunc(variadic ref refcursor[]) RETURNS SETOF
refcursor AS $
begin
open ref[1] for select * from pg_class;
return next ref[1];
open ref[2] for select * from pg_class;
return next ref[2];
end;
$ lANGUAGE plpgsql;
There is a workaround
CREATE FUNCTION myfunc(variadic ref refcursor[]) RETURNS SETOF
refcursor AS $$ declare r refcursor;
begin r = ref[1];
open r for select * from pg_class;
return next ref[1]; r = ref[2];
open r for select * from pg_class;
return next ref[2];
end;
$$ lANGUAGE plpgsql;
CREATE FUNCTION myfunc(variadic ref refcursor[]) RETURNS SETOF
refcursor AS $$ declare r refcursor;
begin r = ref[1];
open r for select * from pg_class;
return next ref[1]; r = ref[2];
open r for select * from pg_class;
return next ref[2];
end;
$$ lANGUAGE plpgsql;
Personally, I have not any idea what do you do. Maybe a C extension can works better for you - PLpgSQL is static strict language. When you use too dynamic code, the result can be hardly maintainable.
Regards
Pavel Stehule
Pavel Stehule <pavel.stehule@gmail.com> writes:
> It is not a bug - it is feature. PLpgSQL statements doesn't expect a
> expression on some places.
Well, it's not unreasonable to expect that a subscripted datum could
be used. It looks to me like this is a grammar omission and the
executor code would work fine.
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #14463: refcursor cann't used with array or variadic parameter?
От
Pavel Stehule
Дата:
2016-12-13 17:45 GMT+01:00 Tom Lane <tgl@sss.pgh.pa.us>:
Pavel Stehule <pavel.stehule@gmail.com> writes:
> It is not a bug - it is feature. PLpgSQL statements doesn't expect a
> expression on some places.
Well, it's not unreasonable to expect that a subscripted datum could
be used. It looks to me like this is a grammar omission and the
executor code would work fine.
There is only one possible issue - the early type check in compile time will be moved to late check in runtime.
Maybe it was reason why somebody didn't allowed a expr there.
Regards
Pavel
regards, tom lane
I wrote:
> Pavel Stehule <pavel.stehule@gmail.com> writes:
>> It is not a bug - it is feature. PLpgSQL statements doesn't expect a
>> expression on some places.
> Well, it's not unreasonable to expect that a subscripted datum could
> be used. It looks to me like this is a grammar omission and the
> executor code would work fine.
Well, not so much. I was thinking in terms of unifying both
getdiag_target and cursor_variable with the assign_var production, but
actually pl_exec.c is only on board with doing that for getdiag_target.
However, we can get it to throw a more sensible error by seeing whether
the next token is '['. I'm not that concerned about whether you can
use an array element in OPEN, but the current error message certainly
looks like a bug rather than an omitted feature.
I've pushed a patch that fixes the error message and also allows
the case for GET DIAGNOSTICS.
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs