add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
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,