Task 1 and 2 plpgsql done

2 files changed, 63 insertions, 0 deletions

diff --git a/challenge-234/luca-ferrari/postgresql/ch-1.sql b/challenge-234/luca-ferrari/postgresql/ch-1.sql
new file mode 100644
index 0000000000..14e45492f1
--- /dev/null
+++ b/challenge-234/luca-ferrari/postgresql/ch-1.sql
@@ -0,0 +1,33 @@
+--
+-- Perl Weekly Challenge 234
+-- Task 1
+--
+-- See <https://perlweeklychallenge.org/blog/perl-weekly-challenge-234/>
+--
+
+CREATE SCHEMA IF NOT EXISTS pwc234;
+
+CREATE OR REPLACE FUNCTION
+pwc234.task1_plpgsql( words text[] )
+RETURNS SETOF char
+AS $CODE$
+DECLARE
+	w text;
+BEGIN
+	CREATE TEMPORARY TABLE IF NOT EXISTS chars( c char, word text );
+	TRUNCATE chars;
+
+	FOREACH w IN ARRAY words LOOP
+		INSERT INTO chars
+		SELECT c, w
+		FROM regexp_split_to_table( w, '' ) c;
+	END LOOP;
+
+	RETURN QUERY
+	       SELECT c
+	       FROM chars
+	       GROUP BY c
+	       HAVING count( word ) >= array_length( words, 1 );
+END
+$CODE$
+LANGUAGE plpgsql;
diff --git a/challenge-234/luca-ferrari/postgresql/ch-2.sql b/challenge-234/luca-ferrari/postgresql/ch-2.sql
new file mode 100644
index 0000000000..1279f673ce
--- /dev/null
+++ b/challenge-234/luca-ferrari/postgresql/ch-2.sql
@@ -0,0 +1,30 @@
+--
+-- Perl Weekly Challenge 234
+-- Task 2
+--
+-- See <https://perlweeklychallenge.org/blog/perl-weekly-challenge-234/>
+--
+
+CREATE SCHEMA IF NOT EXISTS pwc234;
+
+CREATE OR REPLACE FUNCTION
+pwc234.task2_plpgsql( nums int[] )
+RETURNS TABLE (ii int, jj int, kk int )
+AS $CODE$
+BEGIN
+	FOR i IN 1 .. array_length( nums, 1 ) - 2 LOOP
+	    FOR j IN i + 1 .. array_length( nums, 1 ) - 1 LOOP
+	    	FOR k IN j + 1 .. array_length( nums, 1 ) LOOP
+		    IF nums[i] <> nums[j] AND nums[j] <> nums[k] AND nums[k] <> nums[i] THEN
+		       raise info '% % %', i, j, k;
+		       ii := i;
+		       jj := j;
+		       kk := k;
+		       RETURN NEXT;
+		    END IF;
+		END LOOP;
+	    END LOOP;
+	END LOOP;
+END
+$CODE$
+LANGUAGE plpgsql;