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. — a_horse_with_no_name, Jan 14 '14 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, Jan 15 '14 at 02:31

score 1 · Accepted Answer · edited May 23 '17 at 12:29

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?

Using a for loop and if statement in postgres

1 Answers1