Postgresql Update Inside For Loop

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 smalle

Solution 1:

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

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

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

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

updatetableset C =resultWHERE 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!):

CREATEOR REPLACE FUNCTION some_function()
  RETURNS void AS
$BODY$
DECLAREresultint;
BEGIN-- r is a structure that contains an element for each column in the select listFOR r INselect*from table_name
  LOOP
    if r.id ='a'thenresult := r.a * r.b;
    end if;
    if r.id ='b'thenresult := r.a + r.b;
    end if;

    updatetableset C =resultWHERE id = r.id; -- note the where condition that uses the value from the record variableEND 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.