各種別の全組み合わせ用テーブルのレコードを作成したい

前提・実現したいこと

下記のようなテーブル構造のデータベースで、テーブルtypes・values（青枠）を使って、組み合わせ用に使われるテーブルpatterns・pattern_values（ピンク枠）のレコードをSQLかPL/pgSQLで生成したいです。

以前質問させていただいた SQLで各種別の全組み合わせを表示したいで、まずpatternsのレコード生成はできたのですが、pattern_valuesのレコード生成がうまくできず行き詰まっています。

用途としてはmonstersとpatternsを紐づけることで、monstersの各種別を取得することができる仕組みになっています。
（質問のために某スマゲLIKEなデータをテキトウに作ったものです）

sql
1SELECT
2    monsters.id,
3    monsters.name,
4    version_id,
5    array_agg(concat_ws(':',type,value) ORDER BY types.id,values.id) AS type_list
6FROM monsters
7    INNER JOIN patterns ON monsters.patterns_id = patterns.id
8    INNER JOIN versions ON versions.id = patterns.version_id
9    INNER JOIN pattern_values ON patterns.id = pattern_values.pattern_id
10    INNER JOIN values ON pattern_values.value_id = values.id
11    INNER JOIN types ON values.type_id = types.id
12GROUP BY 1,2,3
13ORDER BY 1,3;

実行結果

id	name	version_id	type_list
1	イフリート	1	{撃種:反射,属性:火,戦型:スピード型}
2	リヴァイアサン	1	{撃種:貫通,属性:水,戦型:パワー型}
3	バハムート	2	{撃種:反射,属性:闇,戦型:バランス型}

使用するサンプルデータには、あらかじめpatterns,pattern_valuesに手動で作ったレコードが入っていますが、

sql
1TRUNCATE TABLE patterns,pattern_values RESTART IDENTITY;

を実行してクリアした後、valuesの全レコードを対象に全組み合わせ（patterns = 40レコード version_idは1で）と
組み合わせに対する各種別の値（pattern_values = 40 x 3 = 120）を作成して（v2と同等のもの）、下記SQLで組み合わせとその種別内訳が正常に表示されればOKです。

sql
1SELECT
2    version_id,
3    versions.name AS version_name,
4    pattern_id,
5    array_agg(concat_ws(':',type,value) ORDER BY types.id,values.id) AS type_list
6FROM versions
7    INNER JOIN patterns on versions.id = patterns.version_id
8    INNER JOIN pattern_values ON patterns.id = pattern_values.pattern_id
9    INNER JOIN values ON pattern_values.value_id = values.id
10    INNER JOIN types ON values.type_id = types.id
11GROUP BY 1,2,3
12ORDER BY 1,3;

実行結果(40件)

version_id	version_name	pattern_id	type_list
1	1.0	1	{撃種:反射,属性:火,戦型:パワー型}
1	1.0	2	{撃種:反射,属性:火,戦型:スピード型}
..	..	..	..
1	1.0	40	{撃種:貫通,属性:闇,戦型:バランス型}

使用するデータ

sql
1CREATE TABLE types
2(
3    id      SERIAL NOT NULL,
4    type    VARCHAR
5);
6COMMENT ON COLUMN types.type IS '種別名';
7INSERT INTO types (type) VALUES ('撃種'),('属性'),('戦型');
8--
9CREATE TABLE values
10(
11    id      SERIAL NOT NULL,
12    type_id INTEGER,
13    value   VARCHAR
14);
15COMMENT ON COLUMN values.value IS '種別の値';
16INSERT INTO values (id,type_id, value) VALUES
17(1,1,'反射'),(2,1,'貫通'),
18(3,2,'火'),(4,2,'水'),(5,2,'木'),
19(6,3,'パワー型'),(7,3,'スピード型'),
20(8,2,'光'),(9,2,'闇'),(10,3,'バランス型'),(11,3,'砲撃型');
21--
22CREATE TABLE versions
23(
24    id      SERIAL NOT NULL,
25    name   VARCHAR
26);
27COMMENT ON COLUMN versions.name IS 'バージョン名';
28INSERT INTO versions (id,name) VALUES
29(1,'1.0'),(2,'2.0');
30--
31CREATE TABLE patterns
32(
33    id      SERIAL NOT NULL,
34    version_id INTEGER
35);
36INSERT INTO patterns(version_id)
37VALUES
38(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1), -- 1~12
39(2),(2),(2),(2),(2),(2),(2),(2),(2),(2),(2),(2),(2),(2),(2),(2),(2),(2),(2),(2),
40(2),(2),(2),(2),(2),(2),(2),(2),(2),(2),(2),(2),(2),(2),(2),(2),(2),(2),(2),(2); -- 13~52
41--
42CREATE TABLE pattern_values
43(
44    id      SERIAL NOT NULL,
45    pattern_id INTEGER,
46    value_id   INTEGER
47);
48INSERT INTO pattern_values(pattern_id,value_id)
49VALUES
50-- version1.0
51( 1,1),( 1,3),( 1,6),
52( 2,1),( 2,3),( 2,7),
53( 3,1),( 3,4),( 3,6),
54( 4,1),( 4,4),( 4,7),
55( 5,1),( 5,5),( 5,6),
56( 6,1),( 6,5),( 6,7),
57( 7,2),( 7,3),( 7,6),
58( 8,2),( 8,3),( 8,7),
59( 9,2),( 9,4),( 9,6),
60(10,2),(10,4),(10,7),
61(11,2),(11,5),(11,6),
62(12,2),(12,5),(12,7),
63-- version2.0
64(13, 1),(13, 3),(13, 6),
65(14, 1),(14, 3),(14, 7),
66(15, 1),(15, 3),(15,10),
67(16, 1),(16, 3),(16,11),
68(17, 1),(17, 4),(17, 6),
69(18, 1),(18, 4),(18, 7),
70(19, 1),(19, 4),(19,10),
71(20, 1),(20, 4),(20,11),
72(21, 1),(21, 5),(21, 6),
73(22, 1),(22, 5),(22, 7),
74(23, 1),(23, 5),(23,10),
75(24, 1),(24, 5),(24,11),
76(25, 1),(25, 8),(25, 6),
77(26, 1),(26, 8),(26, 7),
78(27, 1),(27, 8),(27,10),
79(28, 1),(28, 8),(28,11),
80(29, 1),(29, 9),(29, 6),
81(30, 1),(30, 9),(30, 7),
82(31, 1),(31, 9),(31,10),
83(32, 1),(32, 9),(32,11),
84(33, 2),(33, 3),(33, 6),
85(34, 2),(34, 3),(34, 7),
86(35, 2),(35, 3),(35,10),
87(36, 2),(36, 3),(36,11),
88(37, 2),(37, 4),(37, 6),
89(38, 2),(38, 4),(38, 7),
90(39, 2),(39, 4),(39,10),
91(40, 2),(40, 4),(40,11),
92(41, 2),(41, 5),(41, 6),
93(42, 2),(42, 5),(42, 7),
94(43, 2),(43, 5),(43,10),
95(44, 2),(44, 5),(44,11),
96(45, 2),(45, 8),(45, 6),
97(46, 2),(46, 8),(46, 7),
98(47, 2),(47, 8),(47,10),
99(48, 2),(48, 8),(48,11),
100(49, 2),(49, 9),(49, 6),
101(50, 2),(50, 9),(50, 7),
102(51, 2),(51, 9),(51,10),
103(52, 2),(52, 9),(52,11);
104
105CREATE TABLE monsters
106(
107    id      SERIAL NOT NULL,
108    name   VARCHAR,
109    patterns_id INTEGER
110);
111COMMENT ON COLUMN monsters.name IS 'モンスタ名';
112
113INSERT INTO monsters (id,name,patterns_id) VALUES
114(1,'イフリート', 2),
115(2,'リヴァイアサン', 9),
116(3,'バハムート',31);

試したこと

２つのテーブルにインサートを行う必要があるためSQL一撃では難しそうでしたので、下記のようにPL/pgSQLで試行錯誤してみましたが、今のところ解決できていません。

sql
1DO
2$$
3DECLARE
4  c_type CURSOR FOR SELECT * FROM types;
5  c_value CURSOR (in_type_id INTEGER) FOR SELECT * FROM values ... WHERE type_id = in_type_id;
6  r_type RECORD;
7  r_value RECORD;
8  pattern_id INTEGER;
9  value_id INTEGER;
10  version_id INTEGER DEFAULT 1;
11BEGIN
12  FOR r_type IN c_type LOOP
13    FOR r_value IN c_value(r_type.id) LOOP
14      ...
15      INSERT INTO patterns(version_id) VALUES(version_id) RETURNING id INTO pattern_id;
16      ...
17      INSERT INTO pattern_values(pattern_id, value_id) VALUES(pattern_id, value_id);
18      ...
19    END LOOP;
20  END LOOP;
21END;
22$$;

行動規範の内容に同意します

回答1件

自己解決

SQLで各種別の全組み合わせを表示したいのベストアンサーのSQLを参考にPL/pgSQLで対応することができました！
前回の質問から、ご協力いただいた方々ありがとうございましたmm

sql
1DO
2$$
3    DECLARE
4        c_pattern CURSOR FOR
5            WITH RECURSIVE
6                t_values (id, value_id) AS (
7                    SELECT DENSE_RANK() OVER (ORDER BY type_id), values.id
8                    FROM values
9                    ORDER BY type_id, values.id
10                ),
11                patterns (id, pattern) AS (
12                    SELECT id, ARRAY [value_id]
13                    FROM t_values
14                    WHERE id = 1
15                    UNION ALL
16                    SELECT t_values.id, array_append(pattern, value_id)
17                    FROM t_values
18                    JOIN patterns ON t_values.id = patterns.id + 1
19                )
20            SELECT pattern
21            FROM patterns
22            WHERE id = (SELECT MAX(id) FROM patterns);
23        r_pattern     RECORD;
24        pattern_id INTEGER;
25        value_id   INTEGER;
26        version_id INTEGER := 1;
27    BEGIN
28        FOR r_pattern IN c_pattern
29        LOOP
30            INSERT INTO patterns(version_id) VALUES (version_id) RETURNING id INTO pattern_id;
31            FOREACH value_id IN ARRAY r_pattern.pattern
32            LOOP
33                INSERT INTO pattern_values(pattern_id, value_id) VALUES (pattern_id, value_id);
34            END LOOP;
35        END LOOP;
36    END;
37$$;