Horton

Thread

Commits

  1. Improve handling of array elements as getdiag_targets and cursor_variables.

  1. BUG #14463: refcursor cann't used with array or variadic parameter?

    digoal@126.com — 2016-12-13T15:25:48Z

    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

  2. Re: BUG #14463: refcursor cann't used with array or variadic parameter?

    Pavel Stehule <pavel.stehule@gmail.com> — 2016-12-13T16:16:43Z

    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;

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

  3. Re: BUG #14463: refcursor cann't used with array or variadic parameter?

    Tom Lane <tgl@sss.pgh.pa.us> — 2016-12-13T16:45:31Z

    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

  4. Re: BUG #14463: refcursor cann't used with array or variadic parameter?

    Pavel Stehule <pavel.stehule@gmail.com> — 2016-12-13T16:49:18Z

    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
>

  5. Re: BUG #14463: refcursor cann't used with array or variadic parameter?

    Tom Lane <tgl@sss.pgh.pa.us> — 2016-12-13T21:37:38Z

    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