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]]]);
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
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 | 可控制維度遍歷順序 | 迴圈開銷大 |
| UNNEST | 1.00 ms | 原生、語法簡潔、效能佳 | 無法自訂遍歷順序 |
| regexp_split_to_table + array_to_string | 0.85 ms | 純 SQL、極快 | 文字轉換開銷、非結構化操作 |
unnest():標準、高效、可組合於查詢中。RETURN QUERY SELECT ... FROM unnest(...) 直接集合回傳。WITH ORDINALITY 取得元素索引:SELECT * FROM unnest(arr) WITH ORDINALITY AS t(val, idx);PostgreSQL 陣列類型強大且靈活。遍歷陣列首選內建 unnest(),複雜邏輯用 PL/pgSQL 封裝,極致效能可嘗試字串轉換分割。理解各方法特性,依場景選擇最適工具。