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';
========================
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
