Using a for loop and if statement in postgres

Question

How come this is not working? Basically, this proc will update columns in the main buyer table to check if the user has data in other tables.

DO language plpgsql $$
    DECLARE 
    buyer integer;
    BEGIN
    FOR buyer IN SELECT id FROM buyers
    LOOP
      IF (SELECT count(*) FROM invoice WHERE buyer_id = buyer) > 0 THEN
        UPDATE buyers SET has_invoice = true WHERE id = buyer;
      ELSE
        UPDATE buyers SET has_invoice = false WHERE id = buyer;
      END IF;
    END LOOP;
    RETURN;
    END;
$$;

You don't need the loop, this can be done (much more efficiently) with a single update statement. — user330315
– user330315, Commented Jan 14, 2014 at 19:26
Also, if you run this while another connection is updating the invoice table, it can produce wrong results. You need to think about concurrency, or take a table lock so you can ignore it. — Craig Ringer
– Craig Ringer, Commented Jan 15, 2014 at 2:31

Community · Accepted Answer · 2017-05-23 12:29:16Z

1

It is unclear what is "not working". Either way, use this equivalent UPDATE statement instead:

UPDATE buyers b
SET    has_invoice = EXISTS (SELECT 1 id FROM invoice WHERE buyer_id = b.id);

If you don't need redundant storage for performance, you can use a VIEW or generated column for the same purpose. Then the column has_invoice is calculated on the fly and always up to date. Instructions in this closely related answer:
Store common query as column?

edited May 23, 2017 at 12:29

CommunityBot

11 silver badge

answered Jan 14, 2014 at 22:49

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

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

Collectives™ on Stack Overflow

Using a for loop and if statement in postgres

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related