Your aggregate function is smart and fast, but there is a bug. If one string matches the tail of another completely, the
UNION ALL part kicks in to return
LEAST($1, $2). That must instead be something like
CASE WHEN length($1) > length($2) THEN $2 ELSE $1 END. Test with 'match' and 'aa_match'. (See fiddle below.)
Plus, make the function
CREATE OR REPLACE 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)
SELECT CASE WHEN length($1) > length($2) THEN $2 ELSE $1 END -- !
$func$ LANGUAGE sql IMMUTABLE STRICT; -- !
NULL values are ignored and empty strings lead to zero-length common suffix. You may want to treat these special cases differently ...
While we only need the length of the common suffix, a very simple
FINALFUNC returns just that:
CREATE AGGREGATE lcs_len(text) (
SFUNC = lcs_iterate
, STYPE = text
, FINALFUNC = length() -- !
Then your query can look like:
SELECT string_agg(trunc, ', ') AS truncated_names
SELECT left(name, -lcs_len(name) OVER ()) AS trunc
WHERE id = 1
.. using the custom aggregate as window function.
I also tested with Postgres 9.4, and it should work with your outdated Postgres 9.1, but that's too old for me to test. Consider upgrading to a current version.