08-21-12, 01:01 #1Registered User
- Join Date
- Apr 2012
Unanswered: Using non-SELECT variables in WITH RECURSIVE
I've found the following function which I need to use in a project.
WITH RECURSIVE x( s, ind ) AS ( SELECT sud, position( ' ' IN sud ) FROM (SELECT '53 7 6 195 98 6 8 6 34 8 3 17 2 6 6 28 419 5 8 79'::text AS sud) xx UNION ALL SELECT substr( s, 1, ind - 1 ) || z || substr( s, ind + 1 ) , position(' ' IN repeat('x',ind) || substr( s, ind + 1 ) ) FROM x , (SELECT gs::text AS z FROM generate_series(1,9) gs) z WHERE ind > 0 AND NOT EXISTS ( SELECT NULL FROM generate_series(1,9) lp WHERE z.z = substr( s, ( (ind - 1 ) / 9 ) * 9 + lp, 1 ) OR z.z = substr( s, mod( ind - 1, 9 ) - 8 + lp * 9, 1 ) OR z.z = substr( s, mod( ( ( ind - 1 ) / 3 ), 3 ) * 3 + ( ( ind - 1 ) / 27 ) * 27 + lp + ( ( lp - 1 ) / 3 ) * 6 , 1 ) ) ) SELECT s FROM x WHERE ind = 0;
I've been going through the code and have a pretty good understanding of how it works. There are a couple of things that elude me though.
1. I found out that if I change one of the zs to another letter, like this:
, (SELECT gs::text AS y FROM generate_series(1,9) gs) z
2. The function uses a certain sudoku, but I'd like to change it so I can choose a sudoku at runtime. I can't figure out how though, because all the variables for WITH REVERSE seem to be needed everytime there's a SELECT FROM t, and having to include it in every call is rather redundant. So say I change a line to this:
SELECT sud, position( ' ' IN sud ) FROM xx
3. I've tested the function with sudokus that have multiple solutions, and it actually returns all of them. I just need it to return the first one though. I'm thinking that I need to do something like a SELECT WHERE COUNT(SELECT WHERE ind = 0) < 2 or possibly insert a break somewhere but I can't see a way to incorporate that into the current function.