在 Postgres 中去除最長的公共後綴和連接

給定一張桌子t：

id | name
------------
1  | abcfug
1  | deffug
1  | hijfug
2  | etc

我該怎麼做：

select string_agg(strip_lcs(name), ', ') from t where id = 1

返回：

abc, def, hij

注意，如果有幫助，我編寫了一個聚合函式來返回 lcs：

CREATE FUNCTION lcs_iterate(_state TEXT, _value TEXT)
RETURNS TEXT
AS
$$
       SELECT  RIGHT($2, s - 1)
       FROM    generate_series(1, LEAST(LENGTH($1), LENGTH($2))) s
       WHERE   RIGHT($1, s) <> RIGHT($2, s)
       UNION ALL
       SELECT  LEAST($1, $2)
       LIMIT 1;
$$
LANGUAGE 'sql';

CREATE AGGREGATE lcs(TEXT) (SFUNC = lcs_iterate, STYPE = TEXT);

您的聚合函式既聰明又快速，但有一個錯誤。如果一個字元串完全匹配另一個字元串的尾部，則該UNION ALL部分開始返回LEAST($1, $2)。那必須是類似的東西CASE WHEN length($1) > length($2) THEN $2 ELSE $1 END。使用*‘match’和‘aa_match’*進行測試。（見下面的小提琴。）

另外，使功能IMMUTABLE STRICT：

CREATE OR REPLACE FUNCTION lcs_iterate(_state text, _value text)
 RETURNS text AS
$func$
SELECT  right($2, s - 1)
FROM    generate_series(1, least(length($1), length($2))) s
WHERE   right($1, s) <> right($2, s)

UNION   ALL
SELECT  CASE WHEN length($1) > length($2) THEN $2 ELSE $1 END  -- !
LIMIT  1;
$func$ LANGUAGE sql IMMUTABLE STRICT;  -- !

NULL 值被忽略，空字元串導致零長度公共後綴。您可能希望以不同的方式處理這些特殊情況…

雖然我們只需要公共後綴的長度，但一個非常簡單的FINALFUNC返回值就是：

CREATE AGGREGATE lcs_len(text) (
  SFUNC = lcs_iterate
, STYPE = text
, FINALFUNC = length()  -- !
  );

然後您的查詢可能如下所示：

SELECT string_agg(trunc, ', ') AS truncated_names
FROM  (
  SELECT left(name, -lcs_len(name) OVER ()) AS trunc
  FROM   tbl
  WHERE  id = 1
  ) sub;

.. 使用自定義聚合作為視窗函式。

db<>在這裡擺弄

我還使用 Postgres 9.4 進行了測試，它應該可以與您過時的 Postgres 9.1 一起使用，但這對我來說太舊了，無法測試。考慮升級到目前版本。

有關的：

• 是否可以在 Postgres 中包裝聚合函式？

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