Merge pull request #5503 from fluca1978/PWC147

Pwc147 - another PostgreSQL solution to task 2

7 files changed, 51 insertions, 6 deletions

diff --git a/challenge-147/luca-ferrari/blog-1.txt b/challenge-147/luca-ferrari/blog-1.txt
index af17183edb..f5a4859575 100644
--- a/challenge-147/luca-ferrari/blog-1.txt
+++ b/challenge-147/luca-ferrari/blog-1.txt
@@ -1 +1 @@
-https://fluca1978.github.io/2022/01/04/PerlWeeklyChallenge146.html#task1
+https://fluca1978.github.io/2022/01/10/PerlWeeklyChallenge147.html#task1
diff --git a/challenge-147/luca-ferrari/blog-2.txt b/challenge-147/luca-ferrari/blog-2.txt
index 5e5d379514..c3a26988bb 100644
--- a/challenge-147/luca-ferrari/blog-2.txt
+++ b/challenge-147/luca-ferrari/blog-2.txt
@@ -1 +1 @@
-https://fluca1978.github.io/2022/01/04/PerlWeeklyChallenge146.html#task2
+https://fluca1978.github.io/2022/01/10/PerlWeeklyChallenge147.html#task2
diff --git a/challenge-147/luca-ferrari/blog-3.txt b/challenge-147/luca-ferrari/blog-3.txt
index aa716b41b9..1854457738 100644
--- a/challenge-147/luca-ferrari/blog-3.txt
+++ b/challenge-147/luca-ferrari/blog-3.txt
@@ -1 +1 @@
-https://fluca1978.github.io/2022/01/04/PerlWeeklyChallenge146.html#task1pg
+https://fluca1978.github.io/2022/01/10/PerlWeeklyChallenge146.html#task1pg
diff --git a/challenge-147/luca-ferrari/blog-4.txt b/challenge-147/luca-ferrari/blog-4.txt
index 520b859582..63d8983e4e 100644
--- a/challenge-147/luca-ferrari/blog-4.txt
+++ b/challenge-147/luca-ferrari/blog-4.txt
@@ -1 +1 @@
-https://fluca1978.github.io/2022/01/04/PerlWeeklyChallenge146.html#task2pg
+https://fluca1978.github.io/2022/01/10/PerlWeeklyChallenge147.html#task2pg
diff --git a/challenge-147/luca-ferrari/blog-5.txt b/challenge-147/luca-ferrari/blog-5.txt
new file mode 100644
index 0000000000..b872164f21
--- /dev/null
+++ b/challenge-147/luca-ferrari/blog-5.txt
@@ -0,0 +1 @@
+https://fluca1978.github.io/2022/01/10/PerlWeeklyChallenge147.html#task2pgb
diff --git a/challenge-147/luca-ferrari/postgresql/ch-2.sql b/challenge-147/luca-ferrari/postgresql/ch-2.sql
index 61465a21c6..67ed7cdeb3 100644
--- a/challenge-147/luca-ferrari/postgresql/ch-2.sql
+++ b/challenge-147/luca-ferrari/postgresql/ch-2.sql
@@ -66,8 +66,6 @@ BEGIN
            IF FOUND THEN
               SELECT current_tuple.n
                      , other_tuple.n
-                     , current_tuple.p
-                     , other_tuple.p
                      , current_tuple.p + other_tuple.p
                      , abs( current_tuple.p - other_tuple.p )
                      , p1.n
diff --git a/challenge-147/luca-ferrari/postgresql/ch-2b.sql b/challenge-147/luca-ferrari/postgresql/ch-2b.sql
new file mode 100644
index 0000000000..427419b900
--- /dev/null
+++ b/challenge-147/luca-ferrari/postgresql/ch-2b.sql
@@ -0,0 +1,46 @@
+CREATE OR REPLACE FUNCTION
+f_pentagon( n bigint )
+RETURNS bigint
+AS
+$CODE$
+        SELECT ( n * ( 3 * n - 1 ) / 2 );
+$CODE$
+LANGUAGE sql
+IMMUTABLE;
+
+/*
+pentagon_pairs
+----------------
+1020, 2167
+2167, 1020
+(2 rows)
+
+Time: 5820,066 ms (00:05,820)
+
+*/
+WITH RECURSIVE pentagons( n, p )
+AS
+(
+        SELECT 1 AS n
+               , f_pentagon( 1 ) AS p
+
+UNION
+        SELECT p.n + 1
+               , f_pentagon( p.n + 1 )
+        FROM pentagons p
+        WHERE p.n < 5000
+)
+
+SELECT format( '%s, %s', l.n, r.n ) AS pentagon_pairs
+FROM pentagons l, pentagons r
+WHERE EXISTS(
+      SELECT *
+      FROM pentagons ps
+      WHERE ps.p = l.p + r.p
+      )
+AND EXISTS (
+    SELECT *
+    FROM pentagons ps
+    WHERE ps.p = abs( l.p - r.p )
+    )
+;