PostgreSQL SQL - Error handling in pltcl

Author

Error handling in pltcl

Dinesh Pandey

2005-04-18, 3:23 am

Hi,

Can any one tell me how to handle error in PL/TCL language.

On Error/Exception,
In the exception block I want to call a FUNCTION (written in PL/pgSQL).

Thanks
Dinesh Pandey

Michael Fuhr

2005-04-18, 3:23 am

On Mon, Apr 18, 2005 at 09:20:37AM +0530, Dinesh Pandey wrote:
>
> Can any one tell me how to handle error in PL/TCL language.

What kind of error? Have you tried using catch?

> On Error/Exception,
> In the exception block I want to call a FUNCTION (written in PL/pgSQL).

You could use spi_exec to SELECT the function.

If you still have trouble, then please post a simple example that
shows what you're trying to do.

BTW, pgsql-sql probably isn't the best place to ask PL/Tcl questions
since its description is "Discussion for users on SQL related
matters." pgsql-general or pgsql-interfaces might be more appropriate.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Dinesh Pandey

2005-04-18, 3:23 am

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_sta
rt != -1} {
set toemailaddress_finis
h [string first ">" $mailto]
set toemailaddress_start
[expr $toemailaddress_star
t + 1]
set toemailaddress_finis
h [expr $toemailaddress_fini
sh - 1]
set toemailaddress [string range $mailto
$toemailaddress_star
t $toemailaddress_fini
sh]
} else {
set toemailaddress $mailto
}

# Mail Cc Address
set ccemailaddress_start
[string first "<" $mailcc]

if {$ccemailaddress_sta
rt != -1} {
set ccemailaddress_finis
h [string first ">" $mailcc]
set ccemailaddress_start
[expr $ccemailaddress_star
t + 1]
set ccemailaddress_finis
h [expr $ccemailaddress_fini
sh - 1]
set ccemailaddress [string range $mailcc
$toemailaddress_star
t $ccemailaddress_fini
sh]
} else {
set ccemailaddress $mailcc
}

# Mail From Address
set fromemailaddress_sta
rt [string first "<" $mailfrom]

if {$fromemailaddress_s
tart != -1} {
set fromemailaddress_fin
ish [string first ">" $mailfrom]
set fromemailaddress_sta
rt [expr $fromemailaddress_st
art +
1]
set fromemailaddress_fin
ish [expr $fromemailaddress_fi
nish -
1]
set fromemailaddress [string range $mailfrom
$fromemailaddress_st
art $fromemailaddress_fi
nish]
} 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

Sponsored Links