Postgresql update inside a loop
I am fairly new to postgresql and am having trouble updating a null column in a table using a for loop. The table I'm working on is huge, so for the sake of brevity, I'll give you a small example that should make sense. 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 that iterates over all the rows and performs some operation on the values in columns a and b, and then inserts the new value in c. For example, if id = a, update table C = A * B or where id = d, set C = A + B, etc. This will 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 in the end, I would like to go through all the rows in the table and update column C with the value in the "id" column. The function I wrote (which doesn't give any errors, but doesn't update 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 is something stupid in there, I have gone missing, probably around what I am and am returning ... empty in this case. But since I only want to update existing rows, do I need to return something? There are probably easier ways to do this than using a loop, but I would like it to work using this method. If anyone could point me in the right direction or point out something clearly obvious that I am doing wrong, I would appreciate it. Thank you in advance.
source to share
There is no need for a loop or a function, it can be done with a single statement update
:
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 doesn't do anything is this:
update table set C = result
WHERE id = '';
You don't have a row with an empty row in your column id
. Your function as using the wrong formula: when id = 'a' THEN B*C
I guess it should be: then a*b
. Since it C
is NULL
initially, it b*c
also gives null. So even if your update in the loop finds a row, it will update it to NULL
.
You are also fetching values from the cursor incorrectly.
If you really want to do it inefficiently in a loop, 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 then again: if your table is "huge" as you say, a loop is an extremely bad solution. Relational databases are designed to process "sets" of data. Line-by-line processing is an anti-pattern that will almost always have poor performance.
Or, conversely, doing precise operations (like my only example update
) is the best choice.
source to share