diff options
| -rw-r--r-- | challenge-234/luca-ferrari/postgresql/ch-1.sql | 33 | ||||
| -rw-r--r-- | challenge-234/luca-ferrari/postgresql/ch-2.sql | 30 |
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; |