Re: server side cursors update & delete, - Mailing list psycopg

On Tue, Feb 9, 2016 at 8:43 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
> On 02/09/2016 01:31 AM, Vardhan wrote:
>>
>> Hi,
>>
>> The section   '39.7.3.3. UPDATE/DELETE WHERE CURRENT OF' of
>> http://www.postgresql.org/docs/9.2/static/plpgsql-cursors.html
>> specifies the syntax as:
>>
>>    UPDATE table SET ... WHERE CURRENT OF cursor;
>>    DELETE FROM table WHERE CURRENT OF cursor;
>>
>> Is there a way to generate these syntax for update & delete ?
>>
>> Right now, if i get the error that .execute() can't be called more than
>> once !
>
>
> Have you looked at?:
>
> http://initd.org/psycopg/docs/usage.html#server-side-cursors
>
> and
>
> http://initd.org/psycopg/docs/connection.html#connection.cursor
>>


Hi Adrian,
     I did went thru the documentation *twice*, before posting, but
still possible that I missed sth. obvious.

     Actually, the first link states something which conflicts with pg doc:

 "Server side cursor are created in PostgreSQL using the DECLARE
command and subsequently handled using MOVE, FETCH and CLOSE
commands."

   Where as, the pg doc clearly states that UPDATE & DELETE  .. where
CURRENT OF <cursor> are also allowed.

  I was able to achieve what i wanted with this function:

def myexec (c,query,vars=None):
    c = self.connection.cursor()
    c.execute(query + ' WHERE CURRENT OF "' + self.name + '"',vars)
    c.close()

and call as :

c = conn.cursor('c1')
c.execute('select * from TEST;')
c.itersize = 1
for r in c:
    print (r)
    myexec(c,'update TEST set (  num )  =  ( %s )  ',(r[1]+7,))


Now , I totally understand that this is very inefficient because of
several round trips,
and attempt should be made to do these kind of things with server side
functions,
or may be collect id's and update in one go etc.


Thanks
Vardhan


>>
>> --
>> TIA
>> Vardhan
>>
>>
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com