Postgres 11

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

CREATE TABLE tbl(id int, expected_result text, name text);
    
INSERT INTO tbl VALUES
    (1, 'fug', 'abcfug'),
    (1, 'fug', 'deffug'),
    (1, 'fug', 'hijfug'),
    (2, 'etc', 'etc'),
    (3, '', 'completly'),
    (3, '', 'different'),
    (3, 'ident', 'ident'),
    (3, 'ident', 'ident'),
    (4, 'match1', 'aa_match1'),
    (4, 'match1', 'match1'),
    (5, 'match2', 'match2'),
    (5, 'match2', 'aa_match2'),
    (6, '', 'top2_match'),
    (6, '', 'top2_match'),
    (6, '', 'last_not'),
    (7, 'tch', 'top2_match'),
    (7, 'tch', 'top2_match'),
    (7, 'tch', 'tch');
    
18 rows affected

-- buggy
CREATE FUNCTION lcs_iterate_bug(_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;

-- fixed
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;  -- !
CREATE AGGREGATE lcs_bug (text) (SFUNC = lcs_iterate_bug, STYPE = text);
CREATE AGGREGATE lcs     (text) (SFUNC = lcs_iterate    , STYPE = text);

CREATE AGGREGATE lcs_len(text) (
   SFUNC = lcs_iterate
 , STYPE = text
 , FINALFUNC = length
   );
   
SELECT id, expected_result
     , lcs_bug(name)
     , lcs(name)
     , lcs_len(name)
FROM   tbl
GROUP  BY 1, 2
ORDER  BY 1;


id
expected_result
lcs_bug
lcs
lcs_len


1
fug
fug
fug
3

2
etc
etc
etc
3

3
ident
ident
ident
5

3



0

4
match1
aa_match1
match1
6

5
match2
aa_match2
match2
6

6



0

7
tch
tch
tch
3


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


truncated_names


abc, def, hij


-- for multiple IDs:
SELECT id, string_agg(trunc, ', ') AS truncated_names
FROM  (
   SELECT id, left(name, lcs_len(name) OVER (PARTITION BY id) * - 1 ) AS trunc
   FROM   tbl
   ) sub
GROUP  BY 1;
   


id
truncated_names


1
abc, def, hij

2


3
, , ,

4
aa_,

5
, aa_

6
, ,

7
top2_ma, top2_ma,

id	expected_result	lcs_bug	lcs	lcs_len
1	fug	fug	fug	3
2	etc	etc	etc	3
3	ident	ident	ident	5
3				0
4	match1	aa_match1	match1	6
5	match2	aa_match2	match2	6
6				0
7	tch	tch	tch	3