Как написать комбинаторную функцию в postgres?
У меня есть таблица PostgreSQL этой формы:
base_id int | mods smallint[]
3 | {7,15,48}
Мне нужно заполнить таблицу этой формы:
combo_id int | base_id int | mods smallint[]
1 | 3 |
2 | 3 | {7}
3 | 3 | {7,15}
4 | 3 | {7,48}
5 | 3 | {7,15,48}
6 | 3 | {15}
7 | 3 | {15,48}
8 | 3 | {48}
Я думаю, что я мог бы сделать это, используя функцию, которая делает это почти точно, перебирая первую таблицу и записывая комбинации во вторую таблицу: Генерация всех комбинаций в SQL
Но я новичок в Postgres и не могу понять, как это сделать, используя plpgsql. Это не должно быть особенно быстрым; он будет периодически запускаться только на сервере. Первая таблица содержит около 80 записей, и, по приблизительным подсчетам, мы можем ожидать около 2600 записей для второй таблицы.
Кто-нибудь может хотя бы указать мне правильное направление?
Edit: Крейг: У меня есть PostgreSQL 9.0. Я успешно смог использовать UNNEST ():
FOR messvar IN SELECT * FROM UNNEST(mods) AS mod WHERE mod BETWEEN 0 AND POWER(2, @n) - 1
LOOP
RAISE NOTICE '%', messvar;
END LOOP;
но тогда не знал, куда идти дальше.
Edit: Для справки я решил использовать решение Эрвина, добавив одну строку, чтобы добавить нулевой результат ('{}') к каждому набору, а особый случай, который Эрвин ссылается на удаленный:
CREATE OR REPLACE FUNCTION f_combos(_arr integer[], _a integer[] DEFAULT '{}'::integer[], _z integer[] DEFAULT '{}'::integer[])
RETURNS SETOF integer[] LANGUAGE plpgsql AS
$BODY$
DECLARE
i int;
j int;
_up int;
BEGIN
IF array_length(_arr,1) > 0 THEN
_up := array_upper(_arr, 1);
IF _a = '{}' AND _z = '{}' THEN RETURN QUERY SELECT '{}'::int[]; END IF;
FOR i IN array_lower(_arr, 1) .. _up LOOP
FOR j IN i .. _up LOOP
CASE j-i
WHEN 0,1 THEN
RETURN NEXT _a || _arr[i:j] || _z;
ELSE
RETURN NEXT _a || _arr[i:i] || _arr[j:j] || _z;
RETURN QUERY SELECT *
FROM f_combos(_arr[i+1:j-1], _a || _arr[i], _arr[j] || _z);
END CASE;
END LOOP;
END LOOP;
ELSE
RETURN NEXT _arr;
END IF;
END;
$BODY$
Затем я использовал эту функцию для заполнения своей таблицы:
INSERT INTO e_ecosystem_modified (ide_ecosystem, modifiers)
(SELECT ide_ecosystem, f_combos(modifiers) AS modifiers FROM e_ecosystem WHERE ecosystemgroup <> 'modifier' ORDER BY ide_ecosystem, modifiers);
Из 79 строк в моей исходной таблице с максимум 7 элементами в массиве модификаторов запросу потребовалось 250 мс, чтобы заполнить 2630 строк в моей выходной таблице. Фантастика.