postgresql v8.4 函式執行但不起作用

我有一個 postgresql v8.4 數據庫。我需要更新數百個表，每個表大約有 300 萬行數據。

客戶想要添加一個新列（我添加了），現在我正在嘗試使用數據更新新列。

我創建了一些 SQL 語句來進行更新。如果我在 pgAdmin 中手動執行它（只是 sql 語句），這將有效；所以我嘗試將其放入一個函式中以自動執行 UPDATE，如下所示：

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;

所以函式執行；對於每個連續的表，更新往往會減慢一點；日誌語句都出現在日誌文件中。它似乎正在工作；但完成後沒有一張表被更新。

我做錯了什麼？

不能從函式更新表嗎？語言錯了嗎？我應該使用 SQL 而不是 plsql 嗎？

提前致謝…

在 Postgres 9.3 下嘗試原始函式程式碼時，它不會編譯。我不得不更改 2 件小事，我已經評論並標記了1和2。用幾個小表測試函式就像一個魅力——我使用過 Postgres 9.3 和 8.4（參見dbfiddle）。範常式式碼：

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);

功能：1返回（代替：return），2沒有分號。在圓括號中選擇。

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;

然後，為了測試功能：

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
;

– 輸出：參見dbfiddle的最後一部分。

對於更大的表（一百萬行），函式的執行會稍微慢一些，但仍然有效。也許你應該再試一次……

引用自：https://dba.stackexchange.com/questions/174711