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...