From 72f3b81e1bb7cd2720bcb69243e1e4b7585a31f1 Mon Sep 17 00:00:00 2001 From: Luca Ferrari Date: Fri, 17 Mar 2023 12:23:09 +0100 Subject: Task plpgsql done --- challenge-208/luca-ferrari/postgresql/ch-2.sql | 29 ++++++++++++++++++++++++++ 1 file changed, 29 insertions(+) create mode 100644 challenge-208/luca-ferrari/postgresql/ch-2.sql diff --git a/challenge-208/luca-ferrari/postgresql/ch-2.sql b/challenge-208/luca-ferrari/postgresql/ch-2.sql new file mode 100644 index 0000000000..01420d9428 --- /dev/null +++ b/challenge-208/luca-ferrari/postgresql/ch-2.sql @@ -0,0 +1,29 @@ +-- +-- Perl Weekly Challenge 208 +-- Task 2 +-- +-- See +-- + +CREATE SCHEMA IF NOT EXISTS pwc208; + +CREATE OR REPLACE FUNCTION +pwc208.task2_plpgsql( l int[] ) +RETURNS TABLE( v int, d text ) +AS $CODE$ + WITH res AS ( + SELECT v, count( vv ) AS c + FROM generate_series( l[1], l[ array_length( l, 1 ) ] ) v + LEFT JOIN unnest( l ) vv ON vv = v + GROUP BY v + ) + SELECT v, 'Duplicated value ' || v + FROM res + WHERE c > 1 + UNION + SELECT v, 'Missing value ' || v + FROM res + WHERE c = 0; + +$CODE$ +LANGUAGE sql; -- cgit