Re: Cursor With_Hold Performance Workarounds/Optimization

> What other things did you try, and how did they fail? In particular,
> what happened when you used
>
> UPDATE table2
> SET [things based on table1]
> FROM table1 [qualified] JOIN table2 ON ([conditions])

well, it is not the ideal way of doing things but then again this SQL is merely
a consequence of the legacy 4GL language and runtime environment we are running
(and trying to migrate to Postgres). We have a lot of those SQL structures and
would prefer not to change all of them to avoid this situation. Currently there
are also two database backends, one being the old legacy database and the other
being Postgres and we are extremely limited from the (lacking) capabilities of
the old database. We are surely planning to change many SQL statements to make
better use of the database but not at this point for this issue.

The loops are more complex in reality of course, more like:

open cursor for select from table1
loop
{ fetch some entries from cursor
call some external application
do some crazy complicated calculations based on some user input in the UI *
update table2
commit
}

The calculations inside the loop are written in some dynamic high-level
language and cannot easily be translated into SQL.

tl;dr: Existing code base has a lot of these patterns. General solution on
database (interfacing) level is required.