Hi,

I have a question on transactions/isolation levels/etc...
In my PL/pgSQL function main loop goes through inventory list of active devices, for each one executing processing applicable for given device, like:
FOR i in --i is %rowtype
select device_id as device_id,
type as type
from devices_list
where active = 1
LOOP
   (...)
-- here is CASE statement, checking value of 'type' parameter
  (....)
END LOOP; --simple enough, right?

This processing is pretty heavy and takes lot of time... so, I'd like to be able to monitor as processing progresses and I need to be able to say:
-which devices've been processed already
-which ones've not been processed yet

My first idea was to create table, updated by my function each time next device is processed, like:
device_id;status
1;0--done
2;0--done
3;1--processing is running
4;2--to be processed

But... Postgress treats function as single transaction, of course. Hence, I'm not able to see any changes in my progress monitoring table until my main function is finished and all the statuses are set to 0. Which is not really the intent (again, the intent is to be able to monitor which devices are yet to be processed while function is still running!)

My ideas so far (none is perfect, unfortunately)
- move my loop to php/other external piece of code... so it will log-in progress in my function using separate transactions (well, I don't want to use external code, would prefer to stay in PL/pgSQL)
- log to text file (slow and not easy to report later on)

Can I ask for any other suggestions/comments? Is there a way I can have this functionality, please?

Regards,
foo