Split text into chunks

-- three texts: one of 50 characters, one shorter than a window, and one whose last sentence has no full stop
CREATE TEMP TABLE docs AS SELECT * FROM (VALUES (1, 'Ducks swim. Geese fly south. Swans glide on lakes.'), (2, 'Hi there'), (3, 'First one. Then no stop')) t(doc, body);
-- by size: consecutive pieces of 20 characters, the last one short
CREATE TEMP TABLE by_size AS
SELECT doc, 'size' AS how, i // 20 + 1 AS n, substr(body, i + 1, 20) AS chunk FROM docs, range(0, (SELECT max(length(body)) FROM docs), 20) t(i) WHERE i < length(body);
-- by window: 20 characters starting every 10, so neighbours overlap by 10; windows go on until one reaches the last character, and that one may run short
CREATE TEMP TABLE by_window AS
SELECT doc, 'window' AS how, i // 10 + 1 AS n, substr(body, i + 1, 20) AS chunk FROM docs, range(0, (SELECT max(length(body)) FROM docs), 10) t(i) WHERE i = 0 OR i + 10 < length(body);
-- by boundary pattern: each sentence ends at a full stop, and text after the last one is a piece of its own
CREATE TEMP TABLE by_pattern AS
SELECT doc, 'pattern' AS how, generate_subscripts(parts, 1) AS n, unnest(parts) AS chunk FROM (SELECT doc, regexp_extract_all(body, '\S[^.]*\.?') AS parts FROM docs);
CREATE TEMP TABLE chunks AS SELECT * FROM by_size UNION ALL SELECT * FROM by_window UNION ALL SELECT * FROM by_pattern;
SELECT * FROM chunks ORDER BY doc, how, n;
SELECT CASE WHEN ((SELECT list(chunk ORDER BY n) = ['Ducks swim. Geese fl', 'y south. Swans glide', ' on lakes.'] FROM chunks WHERE doc = 1 AND how = 'size')
  AND (SELECT list(chunk ORDER BY n) = ['Ducks swim. Geese fl', '. Geese fly south. S', 'y south. Swans glide', 'wans glide on lakes.'] FROM chunks WHERE doc = 1 AND how = 'window')
  AND (SELECT list(chunk ORDER BY n) = ['Ducks swim.', 'Geese fly south.', 'Swans glide on lakes.'] FROM chunks WHERE doc = 1 AND how = 'pattern')
  AND (SELECT list(chunk ORDER BY how) = ['Hi there', 'Hi there', 'Hi there'] FROM chunks WHERE doc = 2)
  AND (SELECT list(chunk ORDER BY n) = ['First one. Then no s', 'top'] FROM chunks WHERE doc = 3 AND how = 'size')
  AND (SELECT list(chunk ORDER BY n) = ['First one. Then no s', ' Then no stop'] FROM chunks WHERE doc = 3 AND how = 'window')
  AND (SELECT list(chunk ORDER BY n) = ['First one.', 'Then no stop'] FROM chunks WHERE doc = 3 AND how = 'pattern')
  AND (SELECT bool_and(joined = body) FROM (SELECT d.body, string_agg(c.chunk, '' ORDER BY c.n) AS joined FROM docs d JOIN chunks c USING (doc) WHERE c.how = 'size' GROUP BY d.doc, d.body))) THEN 'as expected' ELSE error('the three texts should give 3 chunks of 20, 4 overlapping windows and 3 sentences for the long one, the whole short text in each form, and the unterminated last sentence kept, with the size chunks joining back into every text') END AS verdict;