CODEWITHSUNDEEP
postgresql
GregT
GregT

Reputation: 1320

Why doesn't this function actually update anything?

This is a SQL learning exercise. I have a 'tbl' with a single integer field. There are no indices on this table. I have this function:

CREATE OR REPLACE FUNCTION rcs()                                                                                     
    RETURNS VOID AS $$                                                                                               
DECLARE                                                                                                              
    c CURSOR FOR SELECT * FROM tbl ORDER BY sequence;                                                                
    s INTEGER;                                                                                                       
    r RECORD;                                                                                                        
BEGIN                                                                                                                
    s := 0;                                                                                                          
    OPEN c;                                                                                                          
    LOOP                                                                                                             
        FETCH c INTO r;                                                                                              
        EXIT WHEN NOT FOUND;                                                                                         
        RAISE NOTICE 'got it';                                                                                       
        r.sequence = s;                                                                                              
        s := s + 1;                                                                                                  
        RAISE NOTICE '%', r.sequence;                                                                                
    END LOOP;                                                                                                        
    CLOSE c;                                                                                                         
END;                                                                                                                 
$$ language 'plpgsql'

This loads and runs cleanly and the RAISE statements suggest that the 'sequence' field gets updated to 0, 1 etc. in accordance with the ORDER BY.

However, when I SELECT the table afterwards, the pre-existing values (which happen to all be '6') did not change.

Is this something to do with transactions? I tried fiddling around with COMMIT, etc. to no avail.

This is a freshly installed Postgresql 9.4.4 running on a Linode with no hackery of config files or anything like that, from the 'psql' command line.

EDIT: maybe it's because 'r' isn't actually the DB table, it's some kind of temporary copy of it? If so, please clarify, hopefully what I'm trying to achieve here is obvious (and I know it may be a bit nonsensical, but surely it's possible without resorting to reading the set into Java, etc.)

Upvotes: 0

Views: 41

Answers (1)

user330315
user330315

Reputation:

The actual problem: your function does not contain an UPDATE statement so nothing gets written to disk. r.sequence = s; simply assigns a new value to a variable that is held in memory.

To fix this, you need something like: 

UPDATE tbl
   set sequence = s -- change the actual column in the table
WHERE current of c; -- for the current row of your cursor

If where current of doesn't work, you need to switch that to a "regular" where clause:

UPDATE tbl
   set sequence = s 
WHERE tbl.pk_column = r.pk_column; -- the current primary key value

But a much more efficient solution is to do this in a single statement:

update tbl
   set sequence = tu.new_sequence
from ( 
    select t.pk_column, 
           row_number() over (order by t.sequence) as new_sequence
    from tbl t
) tu
where tbl.pk_column = tu.pk_column;

You need to replace the column name pk_column with the real primary key (or unique) column of your table.

Upvotes: 2

Related Questions