Postgresql Update inside For Loop

Question

I'm new enough to postgresql, and I'm having issues updating a column of null values in a table using a for loop. The table i'm working on is huge so for brevity i'll give a smaller example which should get the point across. Take the following table

+----+----------+----------+
| id  |  A  | B  |  C       |
+----+----------+----------+
| a   | 1   | 0  |  NULL    |
| b   | 1   | 1  |  NULL    |
| c   | 2   | 4  |  NULL    |
| a   | 3   | 2  |  NULL    |
| c   | 2   | 3  |  NULL    |
| d   | 4   | 2  |  NULL    |
+----+----------+----------+

I want to write a for loop which iterates through all of the rows and does some operation on the values in columns a and b and then inserts a new value in c. For example, where id = a , update table set C = A*B, or where id = d set C = A + B etc. This would then give me a table like

+----+----------+----------+
| id  |  A  | B  |  C       |
+----+----------+----------+
| a   | 1   | 0  |  0       |
| b   | 1   | 1  |  NULL    |
| c   | 2   | 4  |  NULL    |
| a   | 3   | 2  |  6       |
| c   | 2   | 3  |  NULL    |
| d   | 4   | 2  |  6       |
+----+----------+----------+

So ultimately I'd like to loop through all the rows of the table and update column C according to the value in the "id" column. The function I've written (which isn't giving any errors but also isn't updating anything either) looks like this...

-- DROP FUNCTION some_function();

CREATE OR REPLACE FUNCTION some_function()
RETURNS void AS
$BODY$
DECLARE
--r integer; not too sure if this needs to be declared or not
result int;

BEGIN

FOR r IN select * from 'table_name' 
LOOP
select(
case
when id = 'a'  THEN B*C
when id = 'd'  THEN B+C
end) 
into result;

update table set C = result 
WHERE id = '';
END LOOP;
RETURN;

END
$BODY$
LANGUAGE plpgsql

I'm sure there's something silly i'm missing, probably around what I'm, returning... void in this case. But as I only want to update existing rows should I need to return anything? There's probably easier ways of doing this than using a loop but I'd like to get it working using this method. If anyone could point me in the right direction or point out anything blatantly obvious that I'm doing wrong I'd much appreciate it. Thanks in advance.

score 19 · Accepted Answer · 2014-08-26 14:11:31Z

No need for a loop or a function, this can be done with a single update statement:

update table_name
  set c = case 
            when id = 'a' then a*b
            when id = 'd' then a+b
            else c -- don't change anything
          end;

SQLFiddle: http://sqlfiddle.com/#!15/b65cb/2

The reason your function isn't doing anything is this:

update table set C = result 
WHERE id = '';

You don't have a row with an empty string in the column id. Your function also seems to use the wrong formula: when id = 'a' THEN B*C I guess that should be: then a*b. As C is NULL initially, b*c will also yield null. So even if your update in the loop would find a row, it would update it to NULL.

You are also retrieving the values incorrectly from the cursor.

If you really, really want to do it inefficiently in a loop, the your function should look something like this (not tested!):

CREATE OR REPLACE FUNCTION some_function()
  RETURNS void AS
$BODY$
DECLARE
  result int;
BEGIN
  -- r is a structure that contains an element for each column in the select list
  FOR r IN select * from table_name
  LOOP
    if r.id = 'a' then 
      result := r.a * r.b;
    end if;
    if r.id = 'b' then 
      result := r.a + r.b;
    end if;

    update table 
      set C = result 
    WHERE id = r.id; -- note the where condition that uses the value from the record variable
  END LOOP;
END
$BODY$
LANGUAGE plpgsql

But again: if your table is "huge" as you say, the loop is an extremely bad solution. Relational databases are made to deal with "sets" of data. Row-by-row processing is an anti-pattern that will almost always have bad performance.

Or to put it the other way round: doing set-based operations (like my single update example) is always the better choice.

Went for the blanket update as per your first answer. I appreciate the time you spent going through the loop, but your right, it wouldn't have been the most efficient solution. Thanks again for the speed of your reply.

Collectives™ on Stack Overflow

Postgresql Update inside For Loop

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related