From a6a67dc89ec8bd9cc6a008e1dc953b57778c265a Mon Sep 17 00:00:00 2001
From: Luca Ferrari <fluca1978@gmail.com>
Date: Mon, 8 May 2023 11:10:31 +0200
Subject: Task 2 plpgsql done

---
 challenge-216/luca-ferrari/postgresql/ch-2.sql | 67 ++++++++++++++++++++++++++
 1 file changed, 67 insertions(+)
 create mode 100644 challenge-216/luca-ferrari/postgresql/ch-2.sql

(limited to 'challenge-216/luca-ferrari/postgresql/ch-2.sql')

diff --git a/challenge-216/luca-ferrari/postgresql/ch-2.sql b/challenge-216/luca-ferrari/postgresql/ch-2.sql
new file mode 100644
index 0000000000..ceca3277e2
--- /dev/null
+++ b/challenge-216/luca-ferrari/postgresql/ch-2.sql
@@ -0,0 +1,67 @@
+--
+-- Perl Weekly Challenge 216
+-- Task 2
+--
+-- See <https://perlweeklychallenge.org/blog/perl-weekly-challenge-216/>
+--
+
+CREATE SCHEMA IF NOT EXISTS pwc216;
+
+CREATE OR REPLACE FUNCTION
+pwc216.task2_plpgsql( word text, stickers text[] )
+RETURNS TABLE ( sticker text, run int, letter text )
+AS $CODE$
+DECLARE
+	cl text;
+	current_sticker text;
+	m int;
+BEGIN
+	CREATE TEMPORARY TABLE IF NOT EXISTS letters( l text, c int DEFAULT 1, s text );
+	TRUNCATE letters;
+
+	INSERT INTO letters( l, c )
+	SELECT ll, count(*)
+	FROM regexp_split_to_table( word, '' ) ll
+	GROUP BY ll;
+
+	FOUND := true;
+	run   := 0;
+	WHILE FOUND LOOP
+	      	run := run + 1;
+
+		PERFORM count(*)
+		FROM letters
+		WHERE c > 0;
+
+		IF NOT FOUND THEN
+		   RETURN;
+		END IF;
+
+		FOR cl IN SELECT l FROM letters WHERE c > 0 LOOP
+		    FOREACH current_sticker IN ARRAY stickers LOOP
+		    	SELECT count(*)
+			INTO m
+			FROM regexp_split_to_table( current_sticker, '' ) s
+			WHERE s = cl;
+
+
+			IF m <= 0 THEN
+			   CONTINUE;
+			END IF;
+
+		     	UPDATE letters
+			SET c = c - m
+			   , s = s || ', ' || current_sticker;
+
+			sticker := current_sticker;
+			letter := cl;
+
+			RETURN NEXT;
+			EXIT;
+		    END LOOP;
+		END LOOP;
+	END LOOP;
+
+END
+$CODE$
+LANGUAGE plpgsql;
-- 
cgit