Inviare e-mail da PostgreSQL

Una delle caratteristiche che contraddistinguono maggiormente PostgreSQL da altre soluzioni database è la capacità di poter eseguire diversi linguaggi di programmazione per la scrittura di funzioni e trigger.
In sostanza PostgreSQL ospita un linguaggio ed esegue il codice espresso in quel linguaggio integrandolo con i dati e i valori passati da un ambiente SQL. Vagamente questo approccia ricorda la programmazione CGI, ove il motore server invoca un programma esterno e lo esegue. La differenza è che nel caso di PostgreSQL il linguaggio viene eseguito in modo perfettamente integrato nel database.

PostgreSQL riconosce due tipi di linguaggi ospite: trusted e untrusted. I primi vengono eseguiti in un ambiente fortemente controllato, e di conseguenza possono eseguire poche operazioni. I linguaggi untrusted invece possono fare tutto quello che è concesso al loro linguaggio (caricare moduli, librerie, ecc.), ma come conseguenza devono essere gestiti da un superutente del database. PostgreSQL quindi permette ad un utente di usare un linguaggio untrusted, a patto che sia proprio l'amministratore del cluster a "confermare" che si è a conoscenza dei rischi che si stanno correndo.

Grazie all'uso dei linguaggi untrusted è possibile usare linguaggi che carichino moduli che ci semplificano diversi compiti, e nel mio caso l'invio di e-mail da una stored procedure.
Per questo ho scelto di usare Perl untrusted (plperlu), che si installa nel database con:

createlang plperlu

Successivamente si puo' creare una stored procedure per l'invio delle e-mail. Siccome in un caso reale si vorra' inviare una e-mail in base a dei dati presenti nel database, il seguente esempio mostra come effettuare una query con le funzioni spi e costruirsi il corpo della e-mail con questi valori. Da notare come la funzioni accetti tre parametri SQL che vengono usati da plperl come normali parametri Perl di una funzione.

CREATE OR REPLACE FUNCTION notifica_email( integer, integer, text )
RETURNS integer AS
$BODY$

  use Mail::Sendmail;
  use MIME::Base64;
  use MIME::QuotedPrint;

  my ($id, $limit, $categoria) = @_;


  # query per estrarre informazioni dalla tabella
  $sql  = " SELECT codice, id_elemento, categoria, email FROM myTable ";
  $sql .= " WHERE id_elemento = $id AND categoria = '$categoria' ";
  $sql .= " LIMIT $limit ";


  # esecuzione della query per ottenere i risultati da processare
  $resultSet = spi_exec_query( $sql );



  # numero di righe restituite
  $numRows = $resultSet->{processed};
  elog(INFO, "Trovate $numRows righe");

  $ret=0;

  ciclo: for( $i = 0; $i <= $numRows; $i++ ){       
      $rigaCorrente = $resultSet->{rows}[$i];

      elog(INFO, "\nIterazione numero $i di $numRows\n");

      # codice dell'elemento corrente
      $codice = $rigaCorrente->{"codice"};
      # categoria di questo elemento
      $categoria = $rigaCorrente->{"categoria"};
      # id elemento
      $id_elemento = $rigaCorrente->{"id_elemento"};
      # email a cui inviare le e-mail
      $email = $rigaCorrente->{"email"};
      # altri campi qui....


      # costruisco il messaggio del warning
      $messaggio = "Messaggio e-mail da PostgreSQL: $id_elemento, $categoria";
   
      # costruisco un soggetto per l'e-mail
      $subject = "Email da PostgreSQL";
      elog(INFO, "\n$subject");

      # email di backup in copia all'amministratore
      $administrator_backup_address = "myself@myself.net";


      # costruzione della mail da inviare
      %mail = ( From    => "postgresql\@postgresql.org",
                To      => "$email",
                Cc      => "$administrator_backup_address",
                Subject => "$subject",
              );

      # invio e-mail
      sendmail( %mail ) or die( $Mail::Sendmail::error) ;
      # incremento il numero di messaggi inviati fino ad ora
      $ret++;

   
  }


   
  # restituisco il numero di messaggi inviati da questa procedura
  return $ret;



$BODY$
LANGUAGE 'plperlu' VOLATILE
COST 100;
ALTER FUNCTION notifica_email() OWNER TO postgres;

E' possibile invocare la funzione con, ad esempio:

select * from notifica_email( 1, 2, 'prodotti' );

Da notare l'uso di elog() per la visualizzazione di messaggi in modo analogo all'uso di RAISE in una funzione plpgsql.