--
-- Simple strip punctuation function in PlPerl
--
 create or replace function strip_punc (text) 
   returns text as
   '
      $_[0] =~ s/\\W|\\s//g ;
      return $_[0];
   ' language 'plperl';

--
-- Min, Max, Average: Example of Query Execution in PlPerl
--
 create or replace function mmav(text,text )
   returns text as
   '
   my $tble = $_[0];
   my $col  = $_[1];
   my $qry  = ''select ''.$col.'' from ''.$tble;
   my $min  = 0;
   my $max  = 0;
   my $sum  = 0;
   
   my $rv = spi_exec_query( $qry );
   elog NOTICE, $rv->{status};
   elog NOTICE, @{$rv->{rows}};
   if (@{$rv->{rows}} == 0 ) { return ''No Rows Found''};
   for ( my $i=0; $i < @{$rv->{rows}} ; $i++ ) {
      if ( $i == 0 ) {
         $sum = $max = $min = $rv->{rows}[$i]->{$col};
         next;
      }
      if ( $max < $rv->{rows}[$i]->{$col} )
         { $max = $rv->{rows}[$i]->{$col}; }
      if ( $min > $rv->{rows}[$i]->{$col} )
         { $min = $rv->{rows}[$i]->{$col}; }
      $sum += $rv->{rows}[$i]->{$col};
   }
   my $result = sprintf(''min=%d max=%d avg=%d'', $min, $max, $sum/@{$rv->{rows}});
   return $result;
   
   ' language 'plperl';


--
-- Big Brother Trigger Function in PlPerl
--
   create table users (
      email text,
      who   text );
   create table bigbro (
      who   text,
      what  text,
      tab   text,
      wwhen timestamp,
      change   text);

   create or replace function bigbro ()
   returns TRIGGER as
   '
   my $changes = "";
   my $qry = "insert into bigbro (who, what, tab, wwhen, change) values (USER, ''";
   $qry .= $_TD->{"event"}."-".$_TD->{"when"}."'', ''".$_TD->{"relname"}."'', now(), ''";

   if ( $_TD->{"event"} eq "INSERT" ){
      $changes .= "who=".$_TD->{"new"}{"who"}.", new.email=".$_TD->{"new"}{"email"}." ";
   }
   if ( $_TD->{"event"} eq "UPDATE" ){
      if ( $_TD->{"new"}{"who"} ne $_TD->{"old"}{"who"})
      {
         $changes .= "old.who=".$_TD->{"old"}{"who"};
         $changes .= ", new.who=".$_TD->{"new"}{"who"}." ";
      }
      if ( $_TD->{"new"}{"email"} ne $_TD->{"old"}{"email"})
      {
         $changes .= "old.email=".$_TD->{"old"}{"email"};
         $changes .= ", new.email=".$_TD->{"new"}{"email"}." ";
      }
   }
   if ( $changes eq "" ) {
      $changes = "No Changes.";
   };
   $qry .= $changes."'');";

   my $rv = spi_exec_query( $qry );
   return;
   ' language 'plperl';

   create trigger insbb before insert on users
   for each row execute procedure bigbro();
   create trigger updbb before update on users
   for each row execute procedure bigbro();

--
-- Min, Max, Average: Example of Query Execution in PlPerl
-- Modified to return one tuple
--
create type mmav_t AS ( mmin float, mmax float, mavg float);

create or replace function mmav(text,text )
returns mmav_t as -- CHANGED
'
   my $tble = $_[0];
   my $col  = $_[1];
   my $qry  = ''select ''.$col.'' from ''.$tble;
   my $min  = 0;
   my $max  = 0;
   my $sum  = 0;
	my $temp = "";
   
   my $rv = spi_exec_query( $qry );
   elog NOTICE, $rv->{status};
   elog NOTICE, @{$rv->{rows}};
   if (@{$rv->{rows}} == 0 ) { return ''No Rows Found''};
   for ( my $i=0; $i < @{$rv->{rows}} ; $i++ ) {
      if ( $i == 0 ) {
         $sum = $max = $min = $rv->{rows}[$i]->{$col};
         next;
      }
      if ( $max < $rv->{rows}[$i]->{$col} )
         { $max = $rv->{rows}[$i]->{$col}; }
      if ( $min > $rv->{rows}[$i]->{$col} )
         { $min = $rv->{rows}[$i]->{$col}; }
      $sum += $rv->{rows}[$i]->{$col};
   }
   return { mmin=>$min, mmax=>$max, mavg=>$sum/@{$rv->{rows}} }; -- CHANGED
   
' language 'plperl';

select * from mmav( 'issues', 'issue');


--
-- Return a set of rows in PlPerl
--
create type team_avg AS ( team text, avg_score float );

create or replace function team_avg()
returns team_avg as
$$
my %ta;
my $avg;
my @rows;
my @teams;
my $qry = 'select team1, score1, team2, score2 from tmatches';
my $rv = spi_exec_query ($qry );

   # elog NOTICE, $rv->{status};
   # elog NOTICE, @{$rv->{rows}};
   for ( my $i=0; $i < @{$rv->{rows}} ; $i++ ) {
      $ta{ $rv->{rows}[$i]->{'team1'} }{'score'} +=  $rv->{rows}[$i]->{'score1'};
      $ta{ $rv->{rows}[$i]->{'team1'} }{'gcount'} +=  1;
      $ta{ $rv->{rows}[$i]->{'team2'} }{'score'} += $rv->{rows}[$i]->{'score2'}; 
      $ta{ $rv->{rows}[$i]->{'team2'} }{'gcount'} +=  1;
   }
   @teams = keys %ta;
   for ( my $i=0 ; $i < @teams ; $i++) {
      elog NOTICE, $ta{ $teams[$i] }->{'score'};
      push @rows, { 'team'=>$teams[$i], 
			'avg_score'=>$ta{$teams[$i]}{'gcount'} == 0 ? 0 : \
				$ta{$teams[$i]}{'score'}/$ta{$teams[$i]}{'gcount'} };

   }
   return \@rows;
$$
language 'plperl';
select sname, avg_score from teams t, team_avg() a where t.sname=a.team;
         

--
-- Using Global Memory
--
create or replace function put_conn_values (text, text)
returns void as 
$$
	$_SHARED{$_[0]}=$_[1];
	
$$ language 'plperl';

create or replace function get_conn_values (text)
returns text as
$$
	return $_SHARED{$_[0]};
	
$$ language 'plperl';