Opening a plpgsql cursor parameter by name

Hello list,

We intend to implement $subject, so instead of

mycursor CURSOR (myparm text) IS SELECT myparm;
OPEN mycursor('A');

it would be possible to do

OPEN mycursor(myparm := 'A');

The idea is to

* in pl_exec.exec_stmt_forc, detect if a positional parameter or named
parameter is used.
* if named, use plpgsql_ns_lookup to find the cursur arguments position
in the plpgsql name space.
* normal processing for the found position from here.

Mixing named and positional could probably be made to work as well.

Does this approach sound reasonable?

regards,
Yeb Havinga & Willem Dijkstra

Yeb Havinga <yebhavinga(at)gmail(dot)com> writes:
> We intend to implement $subject, so instead of

> mycursor CURSOR (myparm text) IS SELECT myparm;
> OPEN mycursor('A');

> it would be possible to do

> OPEN mycursor(myparm := 'A');

Is this really worth the trouble? Is it supported by any other DBMS?
Are cursors used so much, or with so many parameters, that there's a
real benefit to be gained? (I tend to think that FOR loops are better
than cursors 99% of the time.)

I wouldn't be so obstructionist if this syntax weren't in flux.
But seeing that we have hopes of migrating from := to => before
very long, adding another dependency on that choice where it's
not buying a lot of functionality doesn't seem like a good idea.

regards, tom lane

Tom Lane wrote:
> Yeb Havinga <yebhavinga(at)gmail(dot)com> writes:
>
>> We intend to implement $subject, so instead of mycursor CURSOR (myparm text) IS SELECT myparm; OPEN mycursor('A'); it would be possible to do OPEN mycursor(myparm := 'A');
>>
>
> Is this really worth the trouble? Is it supported by any other DBMS?
> Are cursors used so much, or with so many parameters, that there's a
> real benefit to be gained? (I tend to think that FOR loops are better
> than cursors 99% of the time.)
>
Thanks for your reply.

For us it is worth the trouble. We must either implement this, or
manually rewrite cursor use in 140K lines of PL code to be migrated from
another DBMS, where cursors are used more with named parameters calling
than positional. The code contains both the OPEN mycursor(myparm => 'A')
syntax as well as FOR rec IN mycursor(myparm => 'A') LOOP ... (but not
FOR rec IN SELECT .. LOOP)).
> I wouldn't be so obstructionist if this syntax weren't in flux.
> But seeing that we have hopes of migrating from := to => before
> very long, adding another dependency on that choice where it's
> not buying a lot of functionality doesn't seem like a good idea.
>
So maybe it's a good idea that we use the => syntax and do not submit
the patch before the := to => migration is done.

regards,
Yeb Havinga