diff options
| -rw-r--r-- | challenge-208/luca-ferrari/postgresql/ch-2.sql | 29 |
1 files changed, 29 insertions, 0 deletions
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 <https://perlweeklychallenge.org/blog/perl-weekly-challenge-208/> +-- + +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; |