Postgres: while "table has rows" loop...

Question

Let us say, we have the following table my_table:

id   Col1         Col2
1    some data    some data
2    some data    some data 
3    some data    some data  
4    some data    some data

Additionally let us assume that there is an update process of the table and after its execution the table ends up with lower number of rows.

We want to iterate this process until "my_table" has no rows. It is certain that after a finite number of iterations of the update process, the table has zero rows.

How correct is to try sth like this:

while (select count(id) from my_table) != 0 loop
  update process
  ...;
end loop;

Updates in SQL don't delete records, they (possibly) modify the data in records which already exist. — Tim Biegeleisen
– Tim Biegeleisen, Commented Feb 2, 2017 at 13:31
yes, you re right. My omission to mention that the update process concerns not only the 'my_table' but also an 'other_table'. Updating the latter leads to lower number of rows in the first. Thanks for the comment, but the answer below caught the point. — Dino C
– Dino C, Commented Feb 3, 2017 at 9:01

SpliFF · Accepted Answer · 2017-02-02 13:34:09Z

1

You'll need to add transactions to make it reliable, otherwise you have no guarantee that some other user or process won't add a row after you count them but before update runs. You actually need:

while not done
   begin transaction
      if (count rows > 0)
         do something that expects rows
      else
         done
   end transaction

answered Feb 2, 2017 at 13:34

SpliFF

39.1k16 gold badges95 silver badges122 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres: while "table has rows" loop...

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related