PostgreSQL: nested loop

Question

Is it possible to have a loop within another loop? (nested loops) in postgres. Something similar to below:

WHILE rowCounter < totalRows LOOP

    FETCH NEXT FROM retailerIdCursor INTO retailerID;

    --FOR i IN 1..8 LOOP
    --WHILE i < 8 LOOP
    LOOP
        UPDATE sales_fact
        SET retailer_id = retailerID
        WHERE sales_id = rowCounter;

        EXIT WHEN i > 8;
    END LOOP;

    rowCounter = 1+ rowCounter;

END LOOP;

Why oh why would you want to do anything like this? This looks like it should be trivial to rewrite as a single set-based UPDATE. — user
– user, Commented Feb 22, 2011 at 13:26
Yes it's possible, no problem at all. But why do you think you need it? Nested loops are slow, very slow. — Frank Heikens
– Frank Heikens, Commented Feb 22, 2011 at 13:29
Yeah probably the query may be slow. But this is a one time process. I am creating a test dataset and for that only I need this to work. I tried, but when I check the desired change has not happened in the database. — picmate 涅
– picmate 涅, Commented Feb 23, 2011 at 7:36

user330315 · Accepted Answer · 2011-07-16 08:38:12Z

2

I fail to see why you cannot just write

UPDATE sales_fact
   SET retailer_id = retailerID
WHERE sales_id IN(1,2,3,4,5,6,7,8);

as Frank has suggested.

Depending on the statement that selects the retailerId, you might not even need the outer loop as well.

answered Jul 16, 2011 at 8:38

user330315

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

Comments

sftsz · Accepted Answer · 2011-02-22 18:35:14Z

0

You can look at postgresql documentation. http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html

answered Feb 22, 2011 at 18:35

sftsz

3564 silver badges14 bronze badges

2 Comments

picmate 涅 Over a year ago

Hi, thanks for the answer. I actually went through the documentation too. But my function with the nested loops doesn't give me the desired result. Thanks again.

Frank Heikens Over a year ago

The nested loop is useless, just use a simple WHERE sales_id IN(1,2,3,4,5,6,7,8). Now you only need a single UPDATE for 8 different conditions.

Collectives™ on Stack Overflow

PostgreSQL: nested loop

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related