2

I have a Postgres loop as follows, which I am running from the command line via psql:

DO
$do$
BEGIN 
   FOR i IN 1..20000
   -- update some rows
   LOOP
END LOOP;
END; 
$do$;

Is there any way to access the value of the counter i to track the progress of the loop? I've looked in the pgAdmin dashboard with no success.

1
  • Create some service table. Update the value relative to this procedure in it. Set your client to select this data periodically.
    – Akina
    Oct 18, 2020 at 17:43

1 Answer 1

3

By adding raise notice. It will keep printing value of i -- so that you will always have idea about loop progress.

    DO
    $do$
    BEGIN 
    FOR i IN 1..20000
    -- update some rows
    raise notice 'Current Progress Value i:%',i;
     LOOP
    END LOOP;
    END; 

Or more better way.

  1. Create a sql file : vi loop.sql -- add your loop into this file with raise notice.

  2. Run as a job: psql -U username -d database_name -a -f loop.sql &>progress_log

  3. Keep monitoring latest id : tail progress_log

Note: One more tip- I think you need to initialize i - need to include a declare section. Like below:(sample)

    DO
    $do$
    DECLARE
    i int8;
    BEGIN
    -----your code--
    END;
2
  • Thanks. This works perfectly. What do you mean about the declare section, though? It works fine without any declare, and the examples in the documentation don't mention it.
    – amball
    Oct 18, 2020 at 22:30
  • 1
    @amball - good if it's working for your without declaration. I'm modifying my answer - so that you can see how you can declare i . It's pgsql block syntax.
    – channa
    Oct 19, 2020 at 7:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.