一个用postgresql的自定义函数求解数独的程序
从这个网页看到一个用postgresql的自定义函数求解数独的程序，其实核心还是一个利用递归CTE的SQL。
然后把两个计算行列宫的临时表做成物理表。
对问题表中的每行执行上述标量函数，效率和单独的SQL差不多。难题耗时比较长。
postgres@b3490d8427cd:~$ psql psql (17.7 (Debian 17.7-3.pgdg13+1)) Type "help" for help. postgres=# table sudoku9_9; postgres=# CREATE TABLE allnum AS SELECT generate_series(1, 9) AS num ; SELECT 9 postgres=# CREATE TABLE grid AS SELECT (i - 1) * 9 + j AS indexof ,i ,j ,(i - 1) / 3 * 3 + 1 + (j - 1) / 3 AS grp FROM allnum n1(i) ,allnum n2(j) ; SELECT 81 postgres=# CREATE OR REPLACE FUNCTION sudoku(text) RETURNS text LANGUAGE sql IMMUTABLE AS $$ WITH RECURSIVE _sudoku AS ( -- 初期値 SELECT $1 kekka -- 再帰with句で追加されていく行 UNION ALL( WITH latest AS ( SELECT kekka ,(ROW_NUMBER() OVER())::int parallelid FROM _sudoku ) ,gridnum AS ( SELECT latest.parallelid ,indexof, i, j, grp ,substr(latest.kekka, indexof, 1) AS num FROM latest, grid ) ,used AS ( SELECT gridnums.parallelid ,gridnums.indexof ,array_agg(DISTINCT usednums.num :: int) usednumarray FROM gridnum usednums ,gridnum gridnums WHERE usednums.num <> ' ' AND gridnums.num = ' ' AND usednums.parallelid = gridnums.parallelid AND( usednums.i = gridnums.i OR usednums.j = gridnums.j OR usednums.grp = gridnums.grp ) GROUP BY gridnums.parallelid ,gridnums.indexof ) ,insertnum AS ( SELECT parallelid ,indexof ,usednumarray ,DENSE_RANK() OVER( PARTITION BY used.parallelid ORDER BY ARRAY_LENGTH(used.usednumarray, 1) DESC, indexof ) AS insertorder FROM used ) SELECT overlay(latest.kekka placing allnum.num::text FROM insertnum.indexof FOR 1) AS kekka FROM latest ,allnum ,insertnum WHERE latest.parallelid = insertnum.parallelid AND insertnum.insertorder = 1 AND allnum.num <> ALL(insertnum.usednumarray) ) ) SELECT kekka FROM _sudoku WHERE strpos(kekka, ' ') = 0; $$ ; CREATE FUNCTION postgres=# CREATE TABLE sudoku_q AS SELECT 1 id, ' 6 3 1 9 5 2 31 14 678 786529 34567 93 1891456372 24 875 9'::text as question UNION SELECT 2, ' 7 3 6 9152 23 48 7418 65 7 9325 876 9341 9215 37 5 18296' UNION SELECT 3, ' 3 1967 4 8 2 34157 2 62315 68 9 13 68 9 258479 6 2 9143857' UNION SELECT 4, ' 9 8 7 42 6 3 7 1 2 5 836 992 651 475 63491831 8 7452 9521 63' UNION SELECT 5, ' 5 3 6 824 7 1 2954167 1 86 2954 6 7 9312 39784 563241 7 9 5283' ; SELECT 5 postgres=# SELECT * FROM sudoku_q ORDER BY id; id | question ----+----------------------------------------------------------------------------------- 1 | 6 3 1 9 5 2 31 14 678 786529 34567 93 1891456372 24 875 9 2 | 7 3 6 9152 23 48 7418 65 7 9325 876 9341 9215 37 5 18296 3 | 3 1967 4 8 2 34157 2 62315 68 9 13 68 9 258479 6 2 9143857 4 | 9 8 7 42 6 3 7 1 2 5 836 992 651 475 63491831 8 7452 9521 63 5 | 5 3 6 824 7 1 2954167 1 86 2954 6 7 9312 39784 563241 7 9 5283 (5 rows) postgres=# \timing Timing is on. postgres=# SELECT id, sudoku(question) FROM sudoku_q ORDER BY id; id | sudoku ----+----------------------------------------------------------------------------------- 1 | 472931658635842917918765423259314786143678295786529134567293841891456372324187569 2 | 694857123387142659152639784235481967418976532769325841876293415921564378543718296 3 | 458326719672914385913857426341579268896231574725468193137685942584792631269143857 4 | 235976841178342596694158327867419235541283679923765184752634918316897452489521763 5 | 635217849741893526982456731329541678178632954564789312213978465856324197497165283 (5 rows) Time: 35.973 ms postgres=# insert into sudoku_q SELECT 8 AS id, E'8 36 7 9 2 5 7 457 1 3 1 68 85 1 9 4 '; INSERT 0 1 Time: 3.909 ms postgres=# SELECT id, sudoku(question) FROM sudoku_q ORDER BY id; id | sudoku ----+----------------------------------------------------------------------------------- 1 | 472931658635842917918765423259314786143678295786529134567293841891456372324187569 2 | 694857123387142659152639784235481967418976532769325841876293415921564378543718296 3 | 458326719672914385913857426341579268896231574725468193137685942584792631269143857 4 | 235976841178342596694158327867419235541283679923765184752634918316897452489521763 5 | 635217849741893526982456731329541678178632954564789312213978465856324197497165283 8 | 812753649943682175675491283154237896369845721287169534521974368438526917796318452 (6 rows) Time: 4808.878 ms (00:04.809) postgres=#