Merge pull request #7771 from fluca1978/PWC209

Pwc209

12 files changed, 196 insertions, 0 deletions

diff --git a/challenge-209/luca-ferrari/blog-1.txt b/challenge-209/luca-ferrari/blog-1.txt
new file mode 100644
index 0000000000..6a888f82b4
--- /dev/null
+++ b/challenge-209/luca-ferrari/blog-1.txt
@@ -0,0 +1 @@
+https://fluca1978.github.io/2023/03/20/PerlWeeklyChallenge209.html#task1
diff --git a/challenge-209/luca-ferrari/blog-2.txt b/challenge-209/luca-ferrari/blog-2.txt
new file mode 100644
index 0000000000..1be93fddaf
--- /dev/null
+++ b/challenge-209/luca-ferrari/blog-2.txt
@@ -0,0 +1 @@
+https://fluca1978.github.io/2023/03/20/PerlWeeklyChallenge209.html#task2
diff --git a/challenge-209/luca-ferrari/blog-3.txt b/challenge-209/luca-ferrari/blog-3.txt
new file mode 100644
index 0000000000..73fb5583a1
--- /dev/null
+++ b/challenge-209/luca-ferrari/blog-3.txt
@@ -0,0 +1 @@
+https://fluca1978.github.io/2023/03/20/PerlWeeklyChallenge209.html#task1plperl
diff --git a/challenge-209/luca-ferrari/blog-4.txt b/challenge-209/luca-ferrari/blog-4.txt
new file mode 100644
index 0000000000..e6b627d8f9
--- /dev/null
+++ b/challenge-209/luca-ferrari/blog-4.txt
@@ -0,0 +1 @@
+https://fluca1978.github.io/2023/03/20/PerlWeeklyChallenge209.html#task2plperl
diff --git a/challenge-209/luca-ferrari/blog-5.txt b/challenge-209/luca-ferrari/blog-5.txt
new file mode 100644
index 0000000000..c792bda1b1
--- /dev/null
+++ b/challenge-209/luca-ferrari/blog-5.txt
@@ -0,0 +1 @@
+https://fluca1978.github.io/2023/03/20/PerlWeeklyChallenge209.html#task1plpgsql
diff --git a/challenge-209/luca-ferrari/blog-6.txt b/challenge-209/luca-ferrari/blog-6.txt
new file mode 100644
index 0000000000..205be1e3ee
--- /dev/null
+++ b/challenge-209/luca-ferrari/blog-6.txt
@@ -0,0 +1 @@
+https://fluca1978.github.io/2023/03/20/PerlWeeklyChallenge209.html#task2plpgsql
diff --git a/challenge-209/luca-ferrari/postgresql/ch-1.plperl b/challenge-209/luca-ferrari/postgresql/ch-1.plperl
new file mode 100644
index 0000000000..8f410c7f40
--- /dev/null
+++ b/challenge-209/luca-ferrari/postgresql/ch-1.plperl
@@ -0,0 +1,19 @@
+--
+-- Perl Weekly Challenge 209
+-- Task 1
+-- See <https://perlweeklychallenge.org/blog/perl-weekly-challenge-209/>
+--
+
+CREATE SCHEMA IF NOT EXISTS pwc209;
+
+CREATE OR REPLACE FUNCTION
+pwc209.task1_plperl( text )
+RETURNS int
+AS $CODE$
+  my ( $string ) = @_;
+  my @bits = split( '', $string );
+  return 1 if ( @bits % 2 != 0 && @bits[ - 1 ] == 0 );
+  return 1 if ( @bits % 2 == 0 && @bits[ - 1 ] == 0 && @bits[ - 2 ] == 0 );
+  return 0;
+$CODE$
+LANGUAGE plperl;
diff --git a/challenge-209/luca-ferrari/postgresql/ch-1.sql b/challenge-209/luca-ferrari/postgresql/ch-1.sql
new file mode 100644
index 0000000000..916f85b4bb
--- /dev/null
+++ b/challenge-209/luca-ferrari/postgresql/ch-1.sql
@@ -0,0 +1,24 @@
+--
+-- Perl Weekly Challenge 209
+-- Task 1
+--
+-- See <https://perlweeklychallenge.org/blog/perl-weekly-challenge-209/>
+--
+
+CREATE SCHEMA IF NOT EXISTS pwc209;
+
+CREATE OR REPLACE FUNCTION
+pwc209.task1_plpgsql( b text )
+RETURNS int
+AS $CODE$
+DECLARE
+BEGIN
+	IF ( length( b ) % 2 = 0 AND b ~ '00$' ) OR ( length( b ) % 2 = 1 AND b ~ '0$' ) THEN
+	   RETURN 1;
+	ELSE
+	  RETURN 0;
+	END IF;
+
+END
+$CODE$
+LANGUAGE plpgsql;
diff --git a/challenge-209/luca-ferrari/postgresql/ch-2.plperl b/challenge-209/luca-ferrari/postgresql/ch-2.plperl
new file mode 100644
index 0000000000..9545f9bd53
--- /dev/null
+++ b/challenge-209/luca-ferrari/postgresql/ch-2.plperl
@@ -0,0 +1,55 @@
+--
+-- Perl Weekly Challenge 209
+-- Task 2
+-- See <https://perlweeklychallenge.org/blog/perl-weekly-challenge-209/>
+--
+
+CREATE SCHEMA IF NOT EXISTS pwc209;
+
+DROP TABLE IF EXISTS pwc209.accounts;
+CREATE TABLE IF NOT EXISTS pwc209.accounts ( a_name text, a_email text );
+TRUNCATE TABLE pwc209.accounts;
+INSERT INTO pwc209.accounts
+VALUES ( 'A', 'a1@a.com' )
+, ('A', 'a2@a.com' )
+, ( 'B', 'b@b.com'  )
+, ( 'A', 'a3@a.com' )
+, ( 'A', 'a1@a.com'  );
+
+
+CREATE OR REPLACE FUNCTION
+pwc209.task2_plperl()
+RETURNS TABLE( a text, e text[] )
+AS $CODE$
+
+   my $result_set = spi_exec_query( " select a_email, count(*) from pwc209.accounts group by a_email having count(*) > 1 " );
+
+
+
+   my @duplicated_emails;
+   for  ( 0 .. $result_set->{ processed } - 1 ) {
+      my $row = $result_set->{ rows }[ $_ ];
+      push @duplicated_emails,  $row->{ a_email }; 
+   }
+
+
+ 
+   my $query = sprintf qq/ with accs AS ( select distinct a_name from pwc209.accounts where a_email IN (%s) )
+select a.a_name, a_email  from pwc209.accounts a, accs where a.a_name = accs.a_name /
+, join( ',',  map( { "'$_'"  } @duplicated_emails ) );
+   $result_set = spi_exec_query( $query );
+
+   my $to_return = {};
+    for  ( 0 .. $result_set->{ processed } - 1 ) {
+      my $row = $result_set->{ rows }[ $_ ];
+      return_next( $to_return ) if ( $to_return->{ a } && $to_return->{ a } ne $row->{ a_name } );
+
+      $to_return->{ a } = $row->{ a_name };
+      next if ( grep { $_ eq $row->{ a_email } } $to_return->{ e }->@* );
+      push $to_return->{ e }->@*, $row->{ a_email };
+    }   
+
+   return_next( $to_return );
+return undef;
+$CODE$
+LANGUAGE plperl;
diff --git a/challenge-209/luca-ferrari/postgresql/ch-2.sql b/challenge-209/luca-ferrari/postgresql/ch-2.sql
new file mode 100644
index 0000000000..0a9dbbdd2a
--- /dev/null
+++ b/challenge-209/luca-ferrari/postgresql/ch-2.sql
@@ -0,0 +1,31 @@
+--
+-- Perl Weekly Challenge 209
+-- Task 2
+--
+-- See <https://perlweeklychallenge.org/blog/perl-weekly-challenge-209/>
+--
+
+CREATE SCHEMA IF NOT EXISTS pwc209;
+
+DROP TABLE IF EXISTS pwc209.accounts;
+CREATE TABLE IF NOT EXISTS pwc209.accounts ( a_name text, a_email text );
+TRUNCATE TABLE pwc209.accounts;
+INSERT INTO pwc209.accounts
+VALUES ( 'A', 'a1@a.com' )
+, ('A', 'a2@a.com' )
+, ( 'B', 'b@b.com'  )
+, ( 'A', 'a3@a.com' )
+, ( 'A', 'a1@a.com'  );
+
+
+CREATE OR REPLACE FUNCTION
+pwc209.task2_plpgsql()
+RETURNS TABLE( a text, e text )
+AS $CODE$
+
+WITH duplicated_emails AS ( SELECT a_email FROM pwc209.accounts GROUP BY a_email HAVING COUNT(*) > 1 )
+, duplicated_accounts AS ( SELECT a_name FROM pwc209.accounts WHERE a_email IN ( SELECT a_email FROM duplicated_emails ) )
+SELECT distinct( a_name, a_email )
+FROM pwc209.accounts WHERE a_name IN ( SELECT a_name FROM duplicated_accounts );
+$CODE$
+LANGUAGE sql;
diff --git a/challenge-209/luca-ferrari/raku/ch-1.p6 b/challenge-209/luca-ferrari/raku/ch-1.p6
new file mode 100644
index 0000000000..54e536dcb2
--- /dev/null
+++ b/challenge-209/luca-ferrari/raku/ch-1.p6
@@ -0,0 +1,23 @@
+#!raku
+
+#
+# Perl Weekly Challenge 209
+# Task 1
+#
+# See <https://perlweeklychallenge.org/blog/perl-weekly-challenge-209/>
+#
+
+sub MAIN( *@bits where { @bits.grep( * ~~ any(1, 0) ).elems == @bits.elems } ) {
+    my @chars = @bits.rotor( 2, :partial ).map(
+	                                        {
+						       given ( $_.join ) {
+						           when '10' { 'b' }
+						           when '11' { 'c' }
+						           when '0'  { 'a' }
+						           default   { 'z' }
+						       }
+                                                 } );
+
+    '1'.say and exit if ( @chars[ * - 1 ] ~~ 'a' );
+    '0'.say;
+}
diff --git a/challenge-209/luca-ferrari/raku/ch-2.p6 b/challenge-209/luca-ferrari/raku/ch-2.p6
new file mode 100644
index 0000000000..d1de98fb3d
--- /dev/null
+++ b/challenge-209/luca-ferrari/raku/ch-2.p6
@@ -0,0 +1,38 @@
+#!raku
+
+#
+# Perl Weekly Challenge 209
+# Task 2
+#
+# See <https://perlweeklychallenge.org/blog/perl-weekly-challenge-209/>
+#
+
+sub MAIN() {
+
+    my  @accounts =
+	( "A", "a1@a.com", "a2@a.com" ),
+        ( "B", "b1@b.com" ),
+        ( "A", "a3@a.com", "a1@a.com" );
+
+    my @merge;
+
+    for 0 ..^ @accounts.elems {
+	next if ! @accounts[ $_ ];
+	my @current-emails = @accounts[ $_ ][ 1 .. * ];
+	for $_ ^..^ @accounts.elems {
+	    next if ! @accounts[ $_ ];
+	    my @emails = @accounts[ $_ ][ 1 .. * ];
+	    my @match  = @current-emails.grep( $_ )  for @emails;
+	    if ( @match ) {
+		@merge.push: [ @accounts[ $_ ][ 0 ], unique( sort(  |@emails, |@current-emails ) ) ];
+		@accounts[ $_ ] = Nil;
+		last;
+	    }
+	    else {
+		@merge.push: [ @accounts[ $_ ] ];
+	    }
+	}
+    }
+
+    @merge.join( "\n" ).say;
+}