DROP TABLE x CASCADE;
CREATE TABLE ext.x (x1 integer, x2 integer, x3 integer);

CREATE OR REPLACE FUNCTION ext.notify_change() RETURNS trigger AS $$
   use XML::DOM;
   use MIME::Lite;
   use Net::SMTP;

   my $doc = XML::DOM::Document->new;

   my $rv = spi_exec_query("SELECT setting FROM pg_settings WHERE name = 'client_encoding'", 1);
   my $encoding = lc($rv->{rows}[0]->{setting});
   my $xml_pi = $doc->createXMLDecl ('1.0',,$encoding);
   $doc->setXMLDecl($xml_pi);	

   $rv = spi_exec_query("SELECT current_database(), current_timestamp::timestamp(0)");
   my $dbname = $rv->{rows}[0]->{current_database}; my $activated = $rv->{rows}[0]->{timestamp};


   my $chfile = $doc->createElement(lc($_TD->{event}));
   $doc->appendChild($chfile);   

   $rv = spi_exec_query('SELECT CURRENT_USER', 1);
   $chfile->setAttribute('user',$rv->{rows}[0]->{current_user});
   $chfile->setAttribute('table',$_TD->{relname});

   my $row = $doc->createElement('row');
   $chfile->appendChild($row);

   if ($_TD->{event} eq 'INSERT') {
      foreach $key (keys %{$_TD->{new}}) {
         my $knode = $doc->createElement($key);
         $row->appendChild($knode);
         if (defined($_TD->{new}{$key})) {
            $knode->appendChild($doc->createTextNode($_TD->{new}{$key}));
         } else {
            $knode->setAttribute('null','true');
         }
      }
   } elsif ($_TD->{event} eq 'UPDATE') {
      foreach $key (keys %{$_TD->{new}}) {
         if ((defined($_TD->{new}{$key}) != defined($_TD->{old}{$key})) ||
             ((defined($_TD->{new}{$key}) == defined($_TD->{old}{$key})) && $_TD->{new}{$key} ne $_TD->{old}{$key})) {
            my $knode = $doc->createElement($key);
            $row->appendChild($knode);
	    if (defined($_TD->{old}{$key})) {
               $knode->setAttribute('before',$_TD->{old}{$key});
            } else {
               $knode->setAttribute('null_before','true');
            }
            if (defined($_TD->{new}{$key})) {	
               $knode->appendChild($doc->createTextNode($_TD->{new}{$key}));
            } else {
               $knode->setAttribute('null','true');
            }
         }
      }
   } elsif ($_TD->{event} eq 'DELETE') {
      foreach $key (keys %{$_TD->{old}}) {
         my $knode = $doc->createElement($key);
         $row->appendChild($knode);
         if (defined($_TD->{old}{$key})) {
            $knode->appendChild($doc->createTextNode($_TD->{old}{$key}));
         } else {
            $knode->setAttribute('null','true');
         }
      }
   } 

   if ($_TD->{argc} == 3) {
      $msg = MIME::Lite->new (From => $_TD->{args}[0], To => $_TD->{args}[1],
         Subject => $_TD->{args}[2], Type =>'multipart/mixed');
      $msg->attach (Encoding => 'binary', Type => 'TEXT; charset=iso-8859-2',
         Data => "V databázi: $dbname došlo v $activated k změně údajů.\n" .
                 "Detailní popis změn je v příloze dopisu ve formátu XML");
      $msg->attach(Type => "text/xml; charset=$encoding", Data => $doc->toString,
         Disposition => 'attachment', Filename => 'change.xml');
      
      $msg->send_by_sendmail(); 
   } else {
      elog NOTICE, $doc->toString;
   }

   $doc->dispose;	
   return ;
$$ LANGUAGE plperlu;

DROP TRIGGER sum_trig ON x;
CREATE TRIGGER sum_trig 
  AFTER INSERT OR UPDATE OR DELETE ON ext.x
  FOR EACH ROW EXECUTE PROCEDURE ext.notify_change('valka@inway.cz','okbob@inway.cz','test priklad');


INSERT INTO ext.x(x1, x2) VALUES(10,20);
UPDATE ext.x SET x3 = 10, x2 = NULL;
SELECT * FROM ext.x;
DELETE FROM ext.x;