Jasbinder Singh Bali napsal(a): > Instead of that, I re-engineered my while loop in the stored procedure > as follows. ... > I was wondering how to implement the SLEEP functionality here.
Hello.
I can't comment the function itself, but I want to bring something else to your attention. Note, that the stored procedure is always run as a single transaction and by doing the sleep in it, it will probbly run for a long time, or maybe even forever. The problem is that "Long running transactions are evil(tm)"
Postgres, and almost any real database engine for that matter, has problems when there are tansactions that run for a very long time. It prevents the cleanup of stale records, because the engine has to keep them around for this long running transaction.
You might consider doing the actual work in the transaction, but the sleeping in between shoud be done outside.
Note to PG developers: Is there any thought being given to have the PL/pgSQL scripting language outside the function body? Like Ora has? It would be perfect for this case and I remember more than a dozen times in last year when I could have used it and saved some PHP work (and network communiaction).
-- Michal Táborský chief systems architect Internet Mall, a.s. <http://www.MALL.cz>