Task 1 and 2 in plpgsql

2 files changed, 137 insertions, 0 deletions

diff --git a/challenge-168/luca-ferrari/postgresql/ch-1.sql b/challenge-168/luca-ferrari/postgresql/ch-1.sql
new file mode 100644
index 0000000000..96bda1b3c5
--- /dev/null
+++ b/challenge-168/luca-ferrari/postgresql/ch-1.sql
@@ -0,0 +1,68 @@
+-- Perl Weekly Challenge 168
+-- Task 1
+
+CREATE SCHEMA IF NOT EXISTS pwc168;
+
+CREATE OR REPLACE FUNCTION
+pwc168.is_prime( n bigint )
+RETURNS bool
+AS $CODE$
+DECLARE
+        i bigint;
+BEGIN
+        FOR i IN 2 .. n - 1 LOOP
+            IF n % i = 0 THEN
+               RETURN FALSE;
+            END IF;
+        END LOOP;
+
+        RETURN TRUE;
+END
+$CODE$
+LANGUAGE plpgsql;
+
+
+CREATE OR REPLACE FUNCTION
+pwc168.task1_plpgsql( l bigint default 5000 )
+RETURNS SETOF BIGINT
+AS $CODE$
+DECLARE
+        a bigint;
+        b bigint;
+        c bigint;
+        d bigint;
+BEGIN
+        -- bootstrap
+        a := 3;
+        b := 0;
+        c := 2;
+
+        RETURN NEXT a;
+        RETURN NEXT b;
+        RETURN NEXT c;
+
+        WHILE l > 0 LOOP
+           d := a + b;
+           a := b;
+           b := c;
+           c := d;
+
+           RAISE INFO 'Level % value %', l, c;
+           RETURN NEXT c;
+           l := l - 1;
+       END LOOP;
+
+
+RETURN;
+END
+$CODE$
+LANGUAGE plpgsql;
+
+
+-- use more than 50 to get all the numbers
+-- BUT THIS CAN BE VERY SLOW from 70 and beyond!
+SELECT DISTINCT n
+FROM pwc168.task1_plpgsql( 50 ) n
+WHERE pwc168.is_prime( n )
+ORDER BY 1
+LIMIT 13;
diff --git a/challenge-168/luca-ferrari/postgresql/ch-2.sql b/challenge-168/luca-ferrari/postgresql/ch-2.sql
new file mode 100644
index 0000000000..c0bffbf930
--- /dev/null
+++ b/challenge-168/luca-ferrari/postgresql/ch-2.sql
@@ -0,0 +1,69 @@
+-- Perl Weekly Challenge 168
+-- Task 2
+
+CREATE SCHEMA IF NOT EXISTS pwc168;
+
+CREATE OR REPLACE FUNCTION
+pwc168.task2_prime_factors( n int )
+RETURNS SETOF int
+AS $CODE$
+DECLARE
+        i int;
+        p bool;
+BEGIN
+
+        FOR i IN 2 .. n - 1 LOOP
+            p := pwc168.is_prime( i );
+
+            IF p AND n % i = 0  THEN
+               WHILE n % i = 0 LOOP
+                     n := n / i;
+                     RETURN NEXT i;
+               END LOOP;
+            END IF;
+        END LOOP;
+
+RETURN;
+END
+$CODE$
+LANGUAGE plpgsql;
+
+
+/*
+testdb=> select * from pwc168.task2_plpgsql( 10 );
+task2_plpgsql
+---------------
+773
+
+*/
+
+CREATE OR REPLACE FUNCTION
+pwc168.task2_plpgsql( n int DEFAULT 10 )
+RETURNS int
+AS $CODE$
+DECLARE
+        i int;
+        v text;
+        p bool;
+BEGIN
+        v = '0';
+        FOR i IN SELECT * FROM pwc168.task2_prime_factors( n ) LOOP
+            v := v || i;
+        END LOOP;
+
+
+        p := pwc168.is_prime( v::int );
+
+        WHILE NOT p LOOP
+                i := v::int;
+                v = '0';
+                FOR i IN SELECT * FROM pwc168.task2_prime_factors( i ) LOOP
+                    v := v || i;
+                END LOOP;
+                p := pwc168.is_prime( v::int );
+        END LOOP;
+
+        RETURN v::int;
+END
+$CODE$
+LANGUAGE plpgsql;