我正在嘗試根據唯一 ID 從我們的某些資料庫中洗掉重復資料。所有已洗掉的資料應存盤在單獨的表中以供審核。由于它涉及相當多的資料庫以及不同的模式和表，我想開始使用變數來減少出錯的機會和我需要的作業量。
這是我能想到的最好的示例查詢，但它不起作用：
do $$
declare @source_schema varchar := 'my_source_schema';
declare @source_table varchar := 'my_source_table';
declare @target_table varchar := 'my_target_schema' || source_table || '_duplicates'; --target schema and appendix are always the same, source_table is a variable input.
declare @unique_keys varchar := ('1', '2', '3')
begin
select into @target_table
from @source_schema.@source_table
where id in (@unique_keys);
delete from @source_schema.@source_table where export_id in (@unique_keys);
end ;
$$;
查詢語法適用于硬編碼值。
大多數時候，我的變數被視為列或根本無法識別。:(
uj5u.com熱心網友回復：
您需要創建然后使用輸入引數呼叫 plpgsql 程序：
CREATE OR REPLACE PROCEDURE duplicates_suppress
(my_target_schema text, my_source_schema text, my_source_table text, unique_keys text[])
LANGUAGE plpgsql AS
$$
BEGIN
EXECUTE FORMAT(
'WITH list AS (INSERT INTO %1$I.%3$I_duplicates SELECT * FROM %2$I.%3$I WHERE array[id] <@ %4$L :: integer[] RETURNING id)
DELETE FROM %2$I.%3$I AS t USING list AS l WHERE t.id = l.id', my_target_schema, my_source_schema, my_source_table, unique_keys :: text) ;
END ;
$$ ;
該程序duplicates_suppress插入id 位于陣列中的my_target_schema.my_source_table || '_duplicates'行，然后從表中洗掉這些行。my_source_schema.my_source_table unique_keysmy_source_schema.my_source_table
在dbfiddle中查看測驗結果。
uj5u.com熱心網友回復：
如前所述，您需要某種動態 SQL。在一個FUNCTION,PROCEDURE或一個DO陳述句中做它在服務器上。
您應該對PL/pgSQL感到滿意。動態 SQL 不是初學者的玩具。
帶有 a 的示例PROCEDURE，就像 Edouard 已經建議的那樣。您需要 aFUNCTION將其包裝在外部事務中（就像您很可能一樣）。看：
- 何時使用存盤程序/用戶定義函式？
CREATE OR REPLACE PROCEDURE pg_temp.f_archive_dupes(_source_schema text, _source_table text, _unique_keys int[], OUT _row_count int)
LANGUAGE plpgsql AS
$proc$
-- target schema and appendix are always the same, source_table is a variable input
DECLARE
_target_schema CONSTANT text := 's2'; -- hardcoded
_target_table text := _source_table || '_duplicates';
_sql text := format(
'WITH del AS (
DELETE FROM %I.%I
WHERE id = ANY($1)
RETURNING *
)
INSERT INTO %I.%I TABLE del', _source_schema, _source_table
, _target_schema, _target_table);
BEGIN
RAISE NOTICE '%', _sql; -- debug
EXECUTE _sql USING _unique_keys; -- execute
GET DIAGNOSTICS _row_count = ROW_COUNT;
END
$proc$;
稱呼：
CALL pg_temp.f_archive_dupes('s1', 't1', '{1, 3}', 0);
db<>在這里擺弄
我將程式設定為臨時的，因為我認為您不需要永久保留它。每個資料庫創建一次。看：
- 如何在 PostgreSQL 中創建臨時函式？
傳遞的模式和表名是區分大小寫的字串！（與普通 SQL 中不帶引號的識別符號不同。）無論哪種方式，在動態連接 SQL 時都要小心 SQL 注入。看：
- PostgreSQL 列名是否區分大小寫？
- 表名作為 PostgreSQL 函式引數
由于您的樣本值看起來像整數，因此制作了_unique_keys型別int[]（陣列）。integer使用列的實際資料型別id！
該變數_sql保存查詢字串，因此可以在實際執行之前輕松除錯。用于RAISE NOTICE '%', _sql;此目的。
我建議在EXECUTE您確定之前評論該行。
我PROCEDURE回傳了處理的行數。您沒有要求這樣做，但它通常很方便。幾乎不惜任何代價。看：
- 動態 SQL (EXECUTE) 作為 IF 陳述句的條件
- 在應用 LIMIT 之前獲取結果計數的最佳方法
最后但并非最不重要的一點是，DELETE ... RETURNING *在資料修改 CTE中使用。因為它只需要找到行，它的成本大約是單獨SELECTand的一半DELETE。而且它非常安全。如果出現任何問題，無論如何都會回滾整個事務。
兩個單獨的命令也可能遇到并發問題或競爭條件，這些問題可以通過這種方式排除，因為DELETE隱式鎖定要洗掉的行。例子：
- 在 Postgres 資料庫之間復制資料
或者，您可以在客戶端程式中構建陳述句。像 psql，并使用\gexec. 例子：
- 從 SQL DDL 陳述句的現有表中篩選列名
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/415518.html
標籤：
上一篇：交叉表函式結果中出現意外重復
下一篇：如何按順序堆疊遷移學習模型