plpgsql update cursor where current of with dynamic query

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • vituko
    New Member
    • Dec 2006
    • 48

    plpgsql update cursor where current of with dynamic query

    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?
  • vituko
    New Member
    • Dec 2006
    • 48

    #2
    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) ;

    Comment

    Working...