How to include "EXCEPTION" handling block in PL/TCL function. - Mailing list pgsql-patches

From Dinesh Pandey
Subject How to include "EXCEPTION" handling block in PL/TCL function.
Date
Msg-id 20050418051034.BE4705342A@svr1.postgresql.org
Whole thread Raw
List pgsql-patches

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';

 

 

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

 

pgsql-patches by date:

Previous
From: Neil Conway
Date:
Subject: Re: tools/entab/halt.c compile fix
Next
From: "Dinesh Pandey"
Date:
Subject: Exception handling: Oracle's "SQLERRM" keyword option?