Skip site navigation (1) Skip section navigation (2)

How to include "EXCEPTION" handling block in PL/TCL function.

From: "Dinesh Pandey" <dpandey(at)secf(dot)com>
To: <pgsql-general(at)postgresql(dot)org>,<pgsql-interfaces(at)postgresql(dot)org>, <pgsql-patches(at)postgresql(dot)org>,<pgsql-sql(at)postgresql(dot)org>
Subject: How to include "EXCEPTION" handling block in PL/TCL function.
Date: 2005-04-18 05:06:27
Message-ID: 20050418051034.BE4705342A@svr1.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-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';

 

 

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

 

Responses

pgsql-patches by date

Next:From: Dinesh PandeyDate: 2005-04-18 05:16:35
Subject: Exception handling: Oracle's "SQLERRM" keyword option?
Previous:From: Neil ConwayDate: 2005-04-15 04:29:47
Subject: Re: tools/entab/halt.c compile fix

pgsql-general by date

Next:From: Dinesh PandeyDate: 2005-04-18 05:16:35
Subject: Exception handling: Oracle's "SQLERRM" keyword option?
Previous:From: Tom LaneDate: 2005-04-17 20:32:03
Subject: Re: "Could not read block" during vacuum

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group