Thread: 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@gmail.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@gmail.com> writes: > >> We intend to implement $subject, so instead of mycursor CURSOR (myparm text) IS SELECT myparm; OPEN mycursor('A'); itwould 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