Solutions in PostgreSQL

2 files changed, 97 insertions, 0 deletions

diff --git a/challenge-137/luca-ferrari/postgresql/ch-1.sql b/challenge-137/luca-ferrari/postgresql/ch-1.sql
new file mode 100644
index 0000000000..798e9070fb
--- /dev/null
+++ b/challenge-137/luca-ferrari/postgresql/ch-1.sql
@@ -0,0 +1,61 @@
+/*
+testdb=> select * from f_long_year();
+f_long_year
+-------------
+1903
+1908
+1914
+1920
+1925
+1931
+1936
+1942
+1948
+1953
+1959
+1964
+1970
+1976
+1981
+1987
+1992
+1998
+2004
+2009
+2015
+2020
+2026
+2032
+2037
+2043
+2048
+2054
+2060
+2065
+2071
+2076
+2082
+2088
+2093
+2099
+(36 rows)
+
+
+*/
+CREATE OR REPLACE FUNCTION
+f_long_year()
+RETURNS SETOF int
+AS $CODE$
+DECLARE
+        current_year int;
+BEGIN
+        FOR current_year IN 1900 .. 2100 LOOP
+           IF date_part( 'week', make_Date( current_year, 12, 31 ) ) = 53 THEN
+              RETURN NEXT current_year;
+           END IF;
+        END LOOP;
+
+        RETURN;
+END
+$CODE$
+LANGUAGE plpgsql;
diff --git a/challenge-137/luca-ferrari/postgresql/ch-2.sql b/challenge-137/luca-ferrari/postgresql/ch-2.sql
new file mode 100644
index 0000000000..ed96d27b8f
--- /dev/null
+++ b/challenge-137/luca-ferrari/postgresql/ch-2.sql
@@ -0,0 +1,36 @@
+/*
+testdb=> select * from f_lychrel( 59, true );
+INFO:  Found 1111 after 3 iterations
+f_lychrel
+-----------
+0
+*/
+CREATE OR REPLACE FUNCTION
+f_lychrel( n int, verb boolean default false )
+RETURNS smallint
+AS $CODE$
+DECLARE
+        result    bigint := n;
+        iteration int    := 0;
+BEGIN
+        IF n < 10 OR n > 10000 THEN
+           RAISE 'n is out of bounds!';
+        END IF;
+
+        WHILE result < 10000000 AND iteration < 500 LOOP
+              iteration = iteration + 1;
+              result    = result + reverse( result::text )::int;
+              IF result = reverse( result::text )::int THEN
+                 IF verb THEN
+                    RAISE INFO 'Found % after % iterations', result, iteration;
+                 END IF;
+
+                 RETURN 0;
+             END IF;
+
+        END LOOP;
+
+        RETURN 1;
+END
+$CODE$
+LANGUAGE plpgsql;