diff options
| -rw-r--r-- | challenge-216/luca-ferrari/postgresql/ch-2.plperl | 1 | ||||
| -rw-r--r-- | challenge-216/luca-ferrari/postgresql/ch-2.sql | 67 |
2 files changed, 67 insertions, 1 deletions
diff --git a/challenge-216/luca-ferrari/postgresql/ch-2.plperl b/challenge-216/luca-ferrari/postgresql/ch-2.plperl index 5316537322..de9b5baafd 100644 --- a/challenge-216/luca-ferrari/postgresql/ch-2.plperl +++ b/challenge-216/luca-ferrari/postgresql/ch-2.plperl @@ -18,7 +18,6 @@ AS $CODE$ $searching_for->{ $_ }++; } - elog(INFO, "Valori? " . grep( { $_ >= 1 } values( $searching_for->%* ) ) ); my $run = 0; while ( grep( { $_ >= 1 } values( $searching_for->%* ) ) ) { $run++; 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; |