By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3601446 fiddles created (47986 in the last week).
-- recursive function
CREATE OR REPLACE FUNCTION word_permutations(_word text)
RETURNS SETOF text
LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE STRICT AS
$func$
BEGIN
IF length(_word) > 1 THEN
RETURN QUERY
SELECT left(_word, 1) || s || w
FROM (VALUES (''), (' ')) sep(s)
, word_permutations(right(_word, -1)) w;
ELSE
RETURN NEXT _word;
END IF;
END
$func$;
SELECT word_permutations('ABCD');
✓
word_permutations
ABCD
A BCD
AB CD
A B CD
ABC D
A BC D
AB C D
A B C D
…
hidden batch(es)
-- rCTE
WITH RECURSIVE
val(w) AS (SELECT 'ABCD') -- input
, sep(s) AS (VALUES (''), (' '))
, cte AS (
SELECT LEFT(w, 1) AS perm, right(w, -1) AS rest FROM val
UNION ALL
SELECT perm || s || LEFT(rest, 1), right(rest, -1)
FROM cte, sep
WHERE rest <> ''
)
SELECT perm FROM cte WHERE rest = '';
-> Result (cost=0.00..0.01 rows=1 width=0) (actual rows=1 loops=1)
Planning Time: 0.013 ms
Execution Time: 23.584 ms
…
hidden batch(es)
EXPLAIN (ANALYZE, TIMING OFF)
WITH RECURSIVE
val(w) AS (SELECT 'ABCDEFGHIJKLMNOP')
, sep(s) AS (VALUES (''), (' '))
, cte AS (
SELECT LEFT(w, 1) AS perm, right(w, -1) AS rest FROM val
UNION ALL
SELECT perm || s || LEFT(rest, 1), right(rest, -1)
FROM cte, sep
WHERE rest <> ''
)
SELECT perm FROM cte WHERE rest = ''
QUERY PLAN
CTE Scan on cte (cost=10.23..14.30 rows=1 width=32) (actual rows=32768 loops=1)
Filter: (rest = ''::text)
Rows Removed by Filter: 32767
CTE cte
-> Recursive Union (cost=0.00..10.23 rows=181 width=64) (actual rows=65535 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=64) (actual rows=1 loops=1)
-- Laurenz' function
EXPLAIN (ANALYZE, TIMING OFF)
WITH s(s) AS (VALUES ('ABCDEFGHIJKLMNOP'))
SELECT substr(s, 1, 1) ||
string_agg(
CASE WHEN i & (2::numeric ^ p)::bigint = 0 THEN '' ELSE ' ' END ||
substr(s, p + 2, 1),
''
)
FROM s
CROSS JOIN generate_series(0, (2::numeric ^ (length(s) - 1) - 1)::bigint) AS i
CROSS JOIN generate_series(0, length(s) - 2) AS p
GROUP BY s, i;
