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?.
WITH RECURSIVE
OrigSeq AS (SELECT ARRAY[2, 1, 2] AS a),
-- List the indices of our array (which will give us the number of cells too):
Pos AS (SELECT v.* FROM OrigSeq, UNNEST(a) WITH ORDINALITY AS v(n, pos)),
-- Try all decrement possibilities until a field reaches -1.
Combi AS
(
-- We start from the maximum value for each field, which is n - 1 compared to OrigSeq.
SELECT ARRAY_AGG(n - 1 ORDER BY pos) a, 0 mini FROM Pos
UNION
SELECT DISTINCT
ARRAY_AGG(v.n - CASE WHEN v.pos = ptd.pos then 1 else 0 END) OVER w,
MIN(v.n - CASE WHEN v.pos = ptd.pos then 1 else 0 END) OVER w
FROM
Combi prev,
UNNEST(prev.a) WITH ORDINALITY AS v(n, pos),
Pos ptd -- Position To Decrement
WHERE
mini >= 0
WINDOW w AS
(
PARTITION BY prev.a, ptd.pos
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)
)
-- Keep only the completed arrays:
SELECT * from Combi WHERE mini >= 0;
| a | mini |
|---|---|
| {1,0,1} | 0 |
| {0,0,1} | 0 |
| {1,0,0} | 0 |
| {0,0,0} | 0 |
SELECT 4