Hi,
How to include “EXCEPTION”
handling block in PL/TCL function.
I have attached below an e-mail
program written in PL/TCL function.
I want to log messages into a
table from a function “log_error”
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 block here
#sp_exec log_error(………);
LANGUAGE 'pltclu';
========================
