Re: server side cursors update & delete,

On 02/09/2016 07:34 AM, Vardhan wrote:
> 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.

Yeah, I was going back through the docs when you made this post.
Realized the form you wanted is not directly supported. What you want is
a variation of UPDATE/DELETE that walks through a CURSOR. I could see
replicating that by using the psycopg2 named cursor support to 'attach'
to a server-side cursor that has the WHERE condition you want and then
walking through that and then, from docs:

cur2 = conn.cursor('curname')
for record in cur2:     # or cur2.fetchone, fetchmany...
     # do something with record
     pass

where 'do something' is the UPDATE or DELETE.

>
>    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


--
Adrian Klaver
adrian.klaver@aklaver.com