Reply-To: <dpandey@secf.com>
From: "Dinesh Pandey" <dpandey@secf.com>
To: "'Michael Fuhr'" <mike@fuhr.org>
Cc: "'PostgreSQL'" <pgsql-sql@postgresql.org>
Subject: Re: Error handling in pltcl
Date: Mon, 18 Apr 2005 10:10:20 +0530
Organization: Second Foundation India P Ltd.
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Thread-Index: AcVDz75NfP3OJvmPTp20nofDCaNMcQAAGq+Q
In-Reply-To: <20050418043232.GA94872@winnie.fuhr.org>
Message-Id: <20050418044339.B46FB533E3@svr1.postgresql.org>

I have attached below an e-mail program.

I want to log messages into a table from a function written in 'plpgsql'
whenever any exception occurs (like unable to connect/invalid email etc..)

Pls update the code attached below

Thanks
Dinesh Pandey
========================

CREATE OR REPLACE FUNCTION PGMAIL(text, text, text, text, text, text) 
RETURNS INT4 AS '
	set mailfrom 	$1
	set mailto 	$2
	set mailcc 	$3
	set mailserver 	$4
	set mailsubject $5
	set mailmessage $6

# Mail Server	
	set myHost $mailserver

# Mail Server SMTP Port	
	set myPort 25
	set mySock [socket $myHost $myPort]
	
# Mail To Address
	set toemailaddress_start [string first "<" $mailto]
	
	if {$toemailaddress_start != -1} {
		set toemailaddress_finish [string first ">" $mailto]
		set toemailaddress_start [expr $toemailaddress_start + 1]
		set toemailaddress_finish [expr $toemailaddress_finish - 1]
		set toemailaddress [string range $mailto
$toemailaddress_start $toemailaddress_finish]
	} else {
		set toemailaddress $mailto
	}

# Mail Cc Address
	set ccemailaddress_start [string first "<" $mailcc]
	
	if {$ccemailaddress_start != -1} {
		set ccemailaddress_finish [string first ">" $mailcc]
		set ccemailaddress_start [expr $ccemailaddress_start + 1]
		set ccemailaddress_finish [expr $ccemailaddress_finish - 1]
		set ccemailaddress [string range $mailcc
$toemailaddress_start $ccemailaddress_finish]
	} else {
		set ccemailaddress $mailcc
	}

# Mail From Address	
	set fromemailaddress_start [string first "<" $mailfrom]
	
	if {$fromemailaddress_start != -1} {
		set fromemailaddress_finish [string first ">" $mailfrom]
		set fromemailaddress_start [expr $fromemailaddress_start +
1]
		set fromemailaddress_finish [expr $fromemailaddress_finish -
1]
		set fromemailaddress [string range $mailfrom
$fromemailaddress_start $fromemailaddress_finish]
	} else {
		set fromemailaddress $mailfrom
	}
	
	fileevent $mySock writable [list svcHandler $mySock]
		fconfigure $mySock -buffering none
		
		puts $mySock "helo $mailserver"
		
		gets $mySock name
		puts $mySock "mail from: $fromemailaddress"
		
		gets $mySock name
		puts $mySock "rcpt to: $toemailaddress"
		
		gets $mySock name
		puts $mySock "rcpt cc: $ccemailaddress"
		
		gets $mySock name
		puts $mySock "data"
		
		gets $mySock name
		puts $mySock "To: $mailto"
		puts $mySock "Cc: $mailcc"
		puts $mySock "From: $mailfrom"
		puts $mySock "Subject: $mailsubject"
		
		puts $mySock ""
		puts $mySock "$mailmessage"
		
		puts $mySock "."
		gets $mySock name
	
	close $mySock
	
	return 1'

#Exception handling
LANGUAGE 'pltclu';


========================