Postgresql

自動化 COPY 命令 - Postgres 9.2

  • June 14, 2016

我有一個帶有幾個不同 IDS 的備份表…我想按 ID 對這些行進行備份。

  • 該函式必須由 account_id 的限制執行(選擇轉儲(21); - 其中 21 = 函式要觸及的 account_id 的數量
  • 每個 account_id 必須有不同的文件名。以下範例:

例子:

COPY 
(
SELECT * FROM backup_table WHERE id = 1112 AND status = 1
) 
TO '/var/lib/pgsql/1112.sql';

COPY 
(
SELECT * FROM backup_table WHERE id = 1113 AND status = 1
) 
TO '/var/lib/pgsql/1113.sql';

COPY 
(
SELECT * FROM backup_table WHERE id = 1114 AND status = 1
) 
TO '/var/lib/pgsql/1114.sql';

問題:

我可以創建一個函式來自動執行此操作嗎?有數以千計的此類 IDS,並且手動執行需要很長時間。

更新 1:

試圖做:

CREATE or REPLACE FUNCTION dump(integer)
RETURNS integer AS $$

declare
  crtRow record;
begin
     FOR crtRow in execute 'select account_id from backup_table WHERE migrated = 1 AND account_id = '|| $1

     LOOP
   COPY
       (SELECT * FROM backup_table WHERE migrated = 1 AND account_id = crtRow.account_id)
   TO '/var/lib/pgsql/backup/%s.sql';
   end loop;
   return integer;
end

$$ language 'plpgsql';
  • 每個 account_Id 都有一個帶有其 ID 的文件
  • 呼叫該函式時,我想指定我想要轉儲的 account_Ids 的數量

然而它不起作用……

更新 2

CREATE or REPLACE FUNCTION function_1(rows integer)
RETURNS void AS $$

declare
  crtRow record;
begin
     FOR crtRow in EXECUTE 'select DISTINCT(account_id) from backup_table WHERE migrated = 1 AND account_id IN '|| $1

     LOOP
   COPY
       (SELECT * FROM backup_table WHERE migrated = 1 AND account_id = crtRow.account_id)
   TO '/var/lib/pgsql/' || crtrow.account_id || '.csv';
   end loop;
end

$$ language 'plpgsql';

錯誤:

錯誤:“||”處或附近的語法錯誤 第 12 行:到 ‘/var/lib/pgsql/’ || crtrow.account_id |…

更新 3:amacvar 的回答

CREATE or REPLACE FUNCTION function_1(rows integer)
RETURNS void AS $$

begin
execute 'COPY
(
SELECT * FROM backup_table WHERE id = ' || rows || 'AND status = 1
)
TO ''/var/lib/pgsql/'||rows||'.sql''';

end

$$ language 'plpgsql';

上述基於 amacvar 答案的函式有效。問題是我必須指定身份證號碼……

例子:

`select function_1(25)` - Where 25 = id

這對我不利,因為我有數千個 ID,無法手動指定它們。

我需要的是:

與上面相同的函式,但在呼叫函式時要指定 IDS 的 LIMIT:

例子:

select function_1(100)- WHERE 100 = 要複製的 id 數量

  • 我怎樣才能做到這一點?

上面的腳本答案顯然會起作用。

但是,假設您必須,必須,從數據庫中執行它(讓我們知道為什麼:)),下面的程式碼(減去循環等)應該會讓您上路。

順便說一句,你非常接近。

CREATE or REPLACE FUNCTION function_1(rows text)
RETURNS void AS $$
begin
 execute 'COPY (
   SELECT * 
     FROM backup_table
    WHERE id = ' || rows || ' 
      AND status = 1
 ) TO ''/var/lib/pgsql/'||rows||'.dat''';
end
$$ language 'plpgsql';

編輯 1:函式循環範例和格式

CREATE or REPLACE FUNCTION function_loop(rows_arr text[])
RETURNS void AS $$
declare
 crtRow text;
begin
 ForEach crtRow in array rows_arr
 LOOP
    perform function_1(crtRow);  
 end loop;
end
$$ language 'plpgsql';

執行為

select function_loop('{1,2,3}');

請注意,此處不執行任何檢查。

例如,即使表中沒有 id=3,這也會創建一個零字節 3.dat

編輯 2:數千個 id 的一個更可擴展的選項是

CREATE or REPLACE FUNCTION function_loop1(startId int, endId int)
RETURNS void AS $$
declare
 allIds text[];
 crtRow text;
begin
 select array_agg(id::text) into allIds 
   from backup_table
  where status=1 
    and id between startId and endId;
 ForEach crtRow in array allIds
 LOOP
   perform function_1(crtRow);  
 end loop;
end
$$ language 'plpgsql';

引用自:https://dba.stackexchange.com/questions/140786