From aa35267ac114608c6612eaa63676c9b68b2dc24d Mon Sep 17 00:00:00 2001 From: Luca Ferrari Date: Sun, 17 Sep 2023 19:10:30 +0200 Subject: Task 1 and 2 plpgsql done --- challenge-234/luca-ferrari/postgresql/ch-1.sql | 33 ++++++++++++++++++++++++++ challenge-234/luca-ferrari/postgresql/ch-2.sql | 30 +++++++++++++++++++++++ 2 files changed, 63 insertions(+) create mode 100644 challenge-234/luca-ferrari/postgresql/ch-1.sql create mode 100644 challenge-234/luca-ferrari/postgresql/ch-2.sql 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 +-- + +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 +-- + +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; -- cgit