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)),
-- For each position list its possible values:
Poss AS
(
SELECT n - 1 AS n, pos FROM Pos -- Per specification, "2" should give a range from 0 to 1 (2 is the count, not the max), so start one step beyond.
UNION
SELECT n - 1, pos FROM Poss WHERE n > 0
),
-- Start from an empty array, generate as many arrays of 1 element as there are possibilities for cell 1, and so on:
Combi AS
(
SELECT ARRAY[]::int[] a, 0 lvl
UNION
SELECT ARRAY_APPEND(a, n), lvl + 1
FROM Combi, Poss
WHERE Poss.pos = lvl + 1
)
-- Keep only the completed arrays:
SELECT * from Combi WHERE lvl = (SELECT COUNT(1) FROM Pos);
| a | lvl |
|---|---|
| {1,0,1} | 3 |
| {0,0,1} | 3 |
| {1,0,0} | 3 |
| {0,0,0} | 3 |
SELECT 4