PostgreSQL 陣列遍歷完全指南：從自訂函數到內建高效方案

多維陣列建立與測試資料

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) FROM test_array WHERE id = 2;
耗時：~7.78 ms（12 筆記錄）

方案二：自訂函數 - SLICE 切片遍歷

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

測試：相同查詢
耗時：~20.14 ms（較慢，因 WHILE 迴圈開銷）

方案三：內建函數 unnest() - 最佳效能

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

耗時：~1.00 ms （最快，C 語言層級實現）

方案四：字串轉換技巧 - 替代方案

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

原理：陣列 → 逗號字串 → 正則分割為資料列
耗時：~0.85 ms（極快，但涉及型別轉換開銷）

效能對比與選型建議

進階：保留陣列結構的展開

若需保留維度資訊（如索引位置）：

SELECT 
    generate_subscripts(str1, 1) AS dim1,
    generate_subscripts(str1, 2) AS dim2,
    generate_subscripts(str1, 3) AS dim3,
    unnest(str1) AS value
FROM test_array WHERE id = 2;

最佳實踐總結

1. 優先使用 unnest()：PostgreSQL 9.4+ 完整支援多維陣列展開，效能最優。
2. 避免臨時表：自訂函數建立/刪除臨時表開銷大，高併發下易產生鎖爭用。
3. 型別安全：anyarray 多態函數方便，但生產環境建議針對具體型別（如 INT[]）建立專用函數。
4. 大陣列考慮：超過萬筆元素時，unnest() 記憶體佔用較大，可分批處理或使用游標。