萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

PostgreSQL 陣列類型遍歷完全指南:自訂函數、UNNEST、正則分割

建立測試環境


CREATE TABLE test_array (
    id SERIAL PRIMARY KEY,
    str1 INT[][][]  -- 三維整數陣列
);

INSERT INTO test_array VALUES 
(1, ARRAY[[[1,2],[3,4],[5,6]],[[20,30],[40,50],[70,100]]]),
(2, ARRAY[[[100,200],[300,400],[500,600]],[[2000,3000],[4000,5000],[7000,10000]]]);

方法一:自訂 PL/pgSQL 函數 - 簡單展開


CREATE OR REPLACE FUNCTION sp_array2table_simple(anyarray)
RETURNS TABLE (element INT) AS $$
DECLARE
    arr ALIAS FOR $1;
    x INT;
BEGIN
    DROP TABLE IF EXISTS tmp_1;
    CREATE TEMPORARY TABLE tmp_1 (id INT);
    FOREACH x IN ARRAY arr LOOP
        INSERT INTO tmp_1 VALUES (x);
    END LOOP;
    RETURN QUERY SELECT * FROM tmp_1;
END;
$$ LANGUAGE plpgsql;

-- 呼叫
SELECT sp_array2table_simple(str1) AS array_list FROM test_array WHERE id = 2;

結果:12 列 (100, 200, 300, 400, 500, 600, 2000, 3000, 4000, 5000, 7000, 10000),耗時 7.78 ms。

方法二:自訂函數 - 帶切片遍歷


CREATE OR REPLACE FUNCTION sp_array2table(anyarray)
RETURNS TABLE (element INT) AS $$
DECLARE
    arr ALIAS FOR $1;
    x INT[];
    nlen INT := 0;
    i INT := 1;
BEGIN
    DROP TABLE IF EXISTS tmp_1;
    CREATE TEMPORARY TABLE tmp_1 (id INT);
    FOREACH x SLICE 1 IN ARRAY arr LOOP  -- SLICE 1 表示按第一維切片
        nlen := array_length(x, 1);
        i := 1;
        WHILE i <= nlen LOOP
            INSERT INTO tmp_1 VALUES (x[i]);
            i := i + 1;
        END LOOP;
    END LOOP;
    RETURN QUERY SELECT * FROM tmp_1;
END;
$$ LANGUAGE plpgsql;

SELECT sp_array2table(str1) FROM test_array WHERE id = 2;  -- 20.14 ms

方法三:內建 UNNEST 函數 (最高效、最簡潔)


SELECT unnest(str1) AS array_list FROM test_array WHERE id = 2;

結果相同,**耗時 1.00 ms**。unnest 可展開任意維度陣列為集合。

方法四:字串轉換 + 正則分割


SELECT regexp_split_to_table(array_to_string(str1, ','), ',+') AS array_list
FROM test_array WHERE id = 2;

將陣列轉為逗號字串,再用正則分割。**耗時 0.85 ms** (最快),但會經過文字轉換,極大陣列可能有效能/精度考量。

效能比較總結

方法耗時優點缺點
自訂函數 (簡單)7.78 ms邏輯可控、可擴充需建臨時表、PL/pgSQL 開銷
自訂函數 (切片)20.14 ms可控制維度遍歷順序迴圈開銷大
UNNEST1.00 ms原生、語法簡潔、效能佳無法自訂遍歷順序
regexp_split_to_table + array_to_string0.85 ms純 SQL、極快文字轉換開銷、非結構化操作

實務建議

  1. 首選 unnest():標準、高效、可組合於查詢中。
  2. 需自訂遍歷邏輯 (如特定維度順序、過濾) 時,撰寫 PL/pgSQL 函數。
  3. 避免在高頻核心路徑使用臨時表 (方法一、二),改用 RETURN QUERY SELECT ... FROM unnest(...) 直接集合回傳。
  4. PostgreSQL 14+ 支援 WITH ORDINALITY 取得元素索引:SELECT * FROM unnest(arr) WITH ORDINALITY AS t(val, idx);

總結

PostgreSQL 陣列類型強大且靈活。遍歷陣列首選內建 unnest(),複雜邏輯用 PL/pgSQL 封裝,極致效能可嘗試字串轉換分割。理解各方法特性,依場景選擇最適工具。

copyright © 萬盛學電腦網 all rights reserved