Postgresql：針對數組元素的模式匹配？

有沒有辦法對 postgresql 中的數組元素進行模式匹配（如果版本有所不同，則為 9.4）？我有一個聚合函式，除其他外，它返回一個元素數組，例如：

SELECT 
  lognum
  ,array_agg(flightnum) as flightnums 
FROM logs 
GROUP BY lognum;

其中flightnum欄位是包含文本字元串或三或四位數字的 varchar。現在說我想選擇所有航班號以“8”開頭的日誌（所以“800”或“8000”系列航班）。我的第一個想法是做這樣的事情：

SELECT 
  * 
FROM (
     SELECT 
        lognum
        ,array_agg(flightnum) as flightnums 
     FROM logs 
     GROUP BY 
        lognum
    ) s1 
WHERE 
  '8%' like ANY(flightnums);

但是，雖然這不會給出錯誤，但它也不會返回任何結果。我猜這是因為萬用字元在運算符的左側。當然，把它轉過來是：

WHERE ANY(flightnum) like '8%'

給我一個語法錯誤。那麼有什麼方法可以執行此查詢，以便獲得包含以 8（或其他）開頭的航班號的任何行？

---

請注意，這是一個簡化的範例，僅展示了我遇到困難的部分。

我最終做的是編寫一個“換向器”運算符。在 postgres 中，我使用以下兩個命令創建了一個新函式和運算符：

create function like_rev (text, text) returns boolean as $$ select $2 like $1 $$ language SQL;
create operator ~~~~ (procedure = like_rev,  leftarg=text, rightarg=text);

這實際上與 a 相同like，但在比較時顛倒了參數順序。所以現在我可以通過簡單地替換來實現對數組進行萬用字元匹配的目標like，~~~~如下所示：

SELECT 
  * 
FROM (
     SELECT 
        lognum,
        array_agg(flightnum) as flightnums 
     FROM logs 
     GROUP BY 
        lognum
     ) s1 
WHERE 
  '8%' ~~~~ ANY(flightnums);

請注意，這可能不是性能最高的解決方案 - 如果您需要最高性能，Cassandra 的答案很可能會更好。但是，這對我來說效果很好，因為它不需要對查詢結構進行任何更改。

你可以通過做這樣的事情來完成你所要求的。

我創建了一個表格和數據來幫助更好地說明我在做什麼。

CREATE TABLE logs (id serial NOT NULL PRIMARY KEY, lognum int, flightnum int);

INSERT INTO logs (lognum, flightnum) VALUES (1,6);
INSERT INTO logs (lognum, flightnum) VALUES (1,7);
INSERT INTO logs (lognum, flightnum) VALUES (1,8);
INSERT INTO logs (lognum, flightnum) VALUES (2,80);
INSERT INTO logs (lognum, flightnum) VALUES (3,12);
INSERT INTO logs (lognum, flightnum) VALUES (4,8008);

postgres@[local]:5432:postgres:=# SELECT * FROM logs;
id | lognum | flightnum 
----+--------+-----------
13 |      1 |         6
14 |      1 |         7
15 |      1 |         8
16 |      2 |        80
17 |      3 |        12
18 |      4 |      8008
(6 rows)

Time: 0.188 ms
postgres@[local]:5432:postgres:=#

請注意，1、2 和 4 的航班分別為 8、80 和 8008。

現在，使用這個查詢，找到lognumswith 8 as a flightnum。這將您的原始查詢與array_agg, 包裝在它周圍的另一個查詢為數組中的每個成員生成下標，該下標可以任意大。最後，一個外部查詢包裝了它，它使用生成的下標來允許您對flightnums數組的每個成員進行比較，看看它們是否是LIKE '8%'.

SELECT lognum FROM ( 
                     SELECT lognum, 
                     flightnums, 
                     generate_subscripts(flightnums, 1) AS s 
                     FROM ( 
                            SELECT lognum, 
                                   array_agg(flightnum) AS flightnums 
                                   FROM logs 
                                        GROUP BY lognum
                          ) AS t1
                  ) AS t2 
           WHERE flightnums[s]::text LIKE '8%' ORDER BY lognum;

這為您提供以下輸出

postgres@[local]:5432:postgres:=# SELECT lognum FROM (SELECT lognum, flightnums, generate_subscripts(flightnums, 1) AS s FROM ( SELECT lognum, array_agg(flightnum) AS flightnums FROM logs GROUP BY lognum) AS t1) AS t2 WHERE flightnums[s]::text LIKE '8%' ORDER BY lognum;
lognum 
--------
     1
     2
     4
(3 rows)

Time: 0.338 ms
postgres@[local]:5432:postgres:=# 

正如上述數據所預期的那樣。

對於進一步的數組操作需求，我建議閱讀他們在 PostgreSQL 文件數組中的章節

引用自：https://dba.stackexchange.com/questions/117767