PWC 136 in PostgreSQL

2 files changed, 45 insertions, 0 deletions

diff --git a/challenge-136/luca-ferrari/postgresql/ch-1.sql b/challenge-136/luca-ferrari/postgresql/ch-1.sql
new file mode 100644
index 0000000000..ebf5d2becb
--- /dev/null
+++ b/challenge-136/luca-ferrari/postgresql/ch-1.sql
@@ -0,0 +1,16 @@
+CREATE OR REPLACE FUNCTION task1( a int, b int )
+  RETURNS int
+AS $CODE$
+  SELECT gcd( a, b ) % 2;
+  $CODE$
+    LANGUAGE SQL;
+
+/*
+  Example of invocation:
+
+  testdb=> SELECT task1( 8, 24 ) as a8_24, task1( 26, 39 ) as a26_39, task1( 4, 10 ) as a4_10;
+  -[ RECORD 1 ]
+  a8_24  | 0
+  a26_39 | 1
+  a4_10  | 0
+*/
diff --git a/challenge-136/luca-ferrari/postgresql/ch-2.sql b/challenge-136/luca-ferrari/postgresql/ch-2.sql
new file mode 100644
index 0000000000..5436ec8456
--- /dev/null
+++ b/challenge-136/luca-ferrari/postgresql/ch-2.sql
@@ -0,0 +1,29 @@
+
+CREATE OR REPLACE FUNCTION task2( l int default 16)
+  RETURNS SETOF text
+  AS $CODE$
+
+WITH RECURSIVE
+  fib( p, n ) AS (
+    SELECT 1 as p, 1 as n
+    UNION
+    SELECT n, p + n FROM fib WHERE n < l
+  )
+    , permutations AS (
+      SELECT n::text as perm_n, n as perm_sum, array[ 1 ] as pivot
+        FROM fib
+      UNION ALL
+      SELECT permutations.perm_n || ',' || fib.n, perm_sum + fib.n, array_append( pivot, fib.n )
+      FROM permutations, fib
+       WHERE fib.n < l
+       AND position( fib.n::text in perm_n ) = 0
+            
+    )
+  , results as (
+    SELECT array( SELECT perm_n FROM permutations )
+  )
+  SELECT *
+  FROM results;
+
+  $CODE$
+    LANGUAGE sql;