Connecting Tech Pros Worldwide Forums | Help | Site Map

plpgsql update cursor where current of with dynamic query

Member
 
Join Date: Dec 2006
Location: Spain, Galiza, Ferrol
Posts: 48
#1: Oct 16 '09
plpgsql (postgresql 8.3 but I can upgrade)

I can open a cursor with a dynamic query (table / column variable) :
-open cursor for execute '...' ;
But if I want do updates...
- execute 'update ...' where current of cursor ??
Mmm I don't find the way.

Another approach :

-execute 'declare cursor ...' +with hold -for update :(
-execute 'update ... where current of cursor'
But how can I get the reference of the cursor into the function context?
I remember something like synchronizing contexts, but this time I couldn't find nothing about it.

The classical approach :

-for i in execute '...' loop
But to perform an update, I must apply a where clause with its performance penalty, I get no advantage on the current loop.

What do you think about this?

Member
 
Join Date: Dec 2006
Location: Spain, Galiza, Ferrol
Posts: 48
#2: Oct 17 '09

re: plpgsql update cursor where current of with dynamic query


I got an answer in freenode irc, if someone is interested :
A refcursor variable is a string that globaly references a portal/container of the query that stores the cursor. So :
execute 'update '||table_name||' set var=val where current of '||quote_ident(cursor::text) ;
Reply

Tags
cursor, dynamic, plpgsql, update