postgresql v8.4 function runs but doesn't work

Question

I have a postgresql v8.4 database. I need to update several hundred tables with about 3 million rows of data each.

The customer wants a new column added (which I added) and now I'm trying to update the new column with data.

I created some SQL statements to do the update. This works if I run it manually in pgAdmin (just the sql statements); so I tried putting this into a function to automate the UPDATE, like so:

CREATE or REPLACE function update_ee() RETURN void as
$$
DECLARE
  row RECORD; 
BEGIN
   FOR row IN SELECT tablename FROM pg_tables WHERE schemaname = 'public' and tablename like '%_2015060%' order by tablename asc;
   LOOP
   RAISE LOG 'removing index for %', row.tablename;
   -- remove indices
   EXECUTE 'DROP INDEX ' || row.tablename || '_start_idx;';

   RAISE LOG 'starting update on %', row.tablename; 
   -- update table
   EXECUTE 'UPDATE ' || row.tablename || ' set ee = array_to_string(regexp_matches(xml_data, ''<EE_ID>(.+?)</EE_ID>''), '';'') where elnot is NULL and xml_data is not null;'; 

   RAISE LOG 'restoring index on %', row.tablename; 
   -- put indices back
   EXECUTE 'CREATE INDEX ' || row.tablename || '_start_idx on ' || row.tablename || ' USING btree(start);'; 
   END LOOP;
  RETURN;
 END;
$$
LANGUAGE plpgsql;

So the function runs; the updates tend to slow down a bit with each successive table; the log statements all appear in the log file. It appears to be working; but upon finish not one table has been updated.

What did I do wrong?

Is it not possible to update tables from a function? Is the language wrong? Should I use SQL instead of plsql?

Thanks in advance...

Answer 1

When trying the original function code under Postgres 9.3, it would not compile. I had to change 2 minor things, which I have commented and labelled 1 and 2. Testing the function with several small tables worked like a charm - I have used Postgres 9.3 and 8.4 (see dbfiddle). Example code:

create table a_2017 (id int primary key, start int);
create index a_2017_start_idx on a_2017 using btree(start) ;
create table b_2017 (id int primary key, start int);
create index b_2017_start_idx on b_2017 using btree(start) ;
create table c_2017 (id int primary key, start int);
create index c_2017_start_idx on c_2017 using btree(start) ;
create table d_2017 (id int primary key, start int);
create index d_2017_start_idx on d_2017 using btree(start) ;
create table e_2017 (id int primary key, start int);
create index e_2017_start_idx on e_2017 using btree(start) ;


insert into a_2017 values (1,1),(2,2),(3,3),(4,4),(5,5);
insert into b_2017 values (11,11),(22,22),(33,33),(44,44),(55,55);
insert into c_2017 values (111,111),(222,222),(333,333),(444,444),(555,555);
insert into d_2017 values (1111,1111),(2222,2222),(3333,3333),(4444,4444),(5555,5555);
insert into e_2017 values (11111,11111),(22222,22222),(33333,33333),(44444,44444),(55555,55555);

Function: 1 returns (instead of: return), 2 no semicolon. select in round brackets.

create or replace function update_2017() 
returns void -- <--_1_
as $$
DECLARE
  row RECORD; 
BEGIN
   FOR row IN ( 
     SELECT tablename 
     FROM pg_tables 
     WHERE schemaname = 'public' and tablename like '%_2017%' 
     order by tablename asc ) -- <--_2_
   LOOP
      RAISE LOG '-> removing index for %', row.tablename;
      -- remove indices
      EXECUTE 'DROP INDEX ' || row.tablename || '_start_idx;';

      RAISE LOG '--> starting update on %', row.tablename; 
      -- update table
      EXECUTE 'UPDATE ' || row.tablename || ' set start = start*1000;'; 

      RAISE LOG '---> restoring index on %', row.tablename; 
      -- put indices back
      EXECUTE 'CREATE INDEX ' || row.tablename || '_start_idx on ' || row.tablename || ' USING btree(start);'; 
   END LOOP;
   RETURN;
END; $$
LANGUAGE plpgsql;

Then, for testing the function:

select update_2017();

select * from a_2017
union
select * from b_2017
union
select * from c_2017
union
select * from d_2017
union
select * from e_2017
;

-- output: see last section of the dbfiddle.

With bigger tables (one million rows), the execution of the function was slightly slower, but worked nonetheless. Maybe you should have another shot at this ...