Created
December 16, 2018 14:30
-
-
Save ddemidov/27110f1c3d44478e111030f01534afd8 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
with c(id) as (select get_combinations((select array_agg(id order by id) from test), 4)) | |
select id | |
from c | |
where array[1,2,3,4] = (select array_agg(i order by i) from test where array[id] <@ c.id); | |
create or replace function get_combinations(source anyarray, size int) returns setof anyarray as $$ | |
with recursive combinations(combination, indices) as ( | |
select source[i:i], array[i] from generate_subscripts(source, 1) i | |
union all | |
select c.combination || source[j], c.indices || j | |
from combinations c, generate_subscripts(source, 1) j | |
where j > all(c.indices) and | |
array_length(c.combination, 1) < size | |
) | |
select combination from combinations | |
where array_length(combination, 1) = size; | |
$$ language sql; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment