From dpandey@secf.com Mon Apr  18 03:54:44 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 1054353829
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 18 Apr 2005 00:54:44 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 36272-06
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 18 Apr 2005 03:54:39 +0000 (GMT)
Received: from secf.com (209-128-104-244.BAYAREA.NET [209.128.104.244])
	by svr1.postgresql.org (Postfix) with ESMTP id 5B4E553827
	for <pgsql-sql@postgresql.org>; Mon, 18 Apr 2005 00:54:36 -0300 (ADT)
Received: from dinesh by secf.com (MDaemon.PRO.v7.2.2.R)
	with ESMTP id md50000650058.msg
	for <pgsql-sql@postgresql.org>; Sun, 17 Apr 2005 20:54:10 -0700
Reply-To: <dpandey@secf.com>
From: "Dinesh Pandey" <dpandey@secf.com>
To: "'PostgreSQL'" <pgsql-sql@postgresql.org>
Subject: Error handling in pltcl
Date: Mon, 18 Apr 2005 09:20:37 +0530
Organization: Second Foundation India P Ltd.
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0000_01C543F7.EDB3E4E0"
X-Mailer: Microsoft Office Outlook, Build 11.0.5510
Thread-Index: AcVBzSGHLkqIM962Sr6ymk9rB324Jw==
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
X-Authenticated-Sender: dpandey@secf.com
X-Spam-Processed: mail.secf.com, Sun, 17 Apr 2005 20:54:10 -0700
	(not processed: message from valid local sender)
X-MDRemoteIP: 220.227.32.11
X-Return-Path: dpandey@secf.com
X-MDaemon-Deliver-To: pgsql-sql@postgresql.org
X-MDAV-Processed: mail.secf.com, Sun, 17 Apr 2005 20:54:14 -0700
Message-Id: <20050418035436.5B4E553827@svr1.postgresql.org>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=1.394 tagged_above=0 required=5 tests=AWL,
	FORGED_RCVD_HELO, HTML_MESSAGE, MSGID_FROM_MTA_ID
X-Spam-Level: *
X-Archive-Number: 200504/237
X-Sequence-Number: 21130

This is a multi-part message in MIME format.


------=_NextPart_000_0000_01C543F7.EDB3E4E0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit

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


 

------=_NextPart_000_0000_01C543F7.EDB3E4E0
Content-Type: text/html;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Dus-ascii">
<META content=3D"MSHTML 6.00.2800.1491" name=3DGENERATOR></HEAD>
<BODY>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D218280615-15042005>Hi,</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D218280615-15042005></SPAN></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D218280615-15042005>Can =
any one tell me=20
how to handle error in PL/TCL language.</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D218280615-15042005></SPAN></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D218280615-15042005>On =
Error/Exception,=20
</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D218280615-15042005>In the =
exception=20
block I want to call a FUNCTION (written in =
PL/pgSQL).</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<P><FONT face=3DVerdana><SPAN class=3D218280615-15042005></SPAN><FONT =
size=3D2><FONT=20
face=3DArial>T<SPAN =
class=3D218280615-15042005>hanks</SPAN></FONT><BR>Dinesh=20
Pandey</FONT></FONT><FONT face=3D"Times New Roman"><BR></FONT></P>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV></BODY></HTML>

------=_NextPart_000_0000_01C543F7.EDB3E4E0--




From mike@fuhr.org Mon Apr  18 04:32:47 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id B552E53419
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 18 Apr 2005 01:32:47 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 46578-08
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 18 Apr 2005 04:32:38 +0000 (GMT)
Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158])
	by svr1.postgresql.org (Postfix) with ESMTP id 93B5C533E6
	for <pgsql-sql@postgresql.org>; Mon, 18 Apr 2005 01:32:36 -0300 (ADT)
Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1])
	by tigger.fuhr.org (8.13.3/8.13.3) with ESMTP id j3I4WX5w028926
	(version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK);
	Sun, 17 Apr 2005 22:32:35 -0600 (MDT)
	(envelope-from mfuhr@winnie.fuhr.org)
Received: from winnie.fuhr.org (localhost [127.0.0.1])
	by winnie.fuhr.org (8.13.3/8.13.3) with ESMTP id j3I4WXvk094986;
	Sun, 17 Apr 2005 22:32:33 -0600 (MDT)
	(envelope-from mfuhr@winnie.fuhr.org)
Received: (from mfuhr@localhost)
	by winnie.fuhr.org (8.13.3/8.13.3/Submit) id j3I4WWB9094985;
	Sun, 17 Apr 2005 22:32:32 -0600 (MDT) (envelope-from mfuhr)
Date: Sun, 17 Apr 2005 22:32:32 -0600
From: Michael Fuhr <mike@fuhr.org>
To: Dinesh Pandey <dpandey@secf.com>
Cc: "'PostgreSQL'" <pgsql-sql@postgresql.org>
Subject: Re: Error handling in pltcl
Message-ID: <20050418043232.GA94872@winnie.fuhr.org>
References: <20050418035436.5B4E553827@svr1.postgresql.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20050418035436.5B4E553827@svr1.postgresql.org>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.004 tagged_above=0 required=5 tests=AWL
X-Spam-Level: 
X-Archive-Number: 200504/238
X-Sequence-Number: 21131

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/



From dpandey@secf.com Mon Apr  18 04:43:49 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 97A73533FE
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 18 Apr 2005 01:43:49 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 49206-10
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 18 Apr 2005 04:43:41 +0000 (GMT)
Received: from secf.com (209-128-104-244.BAYAREA.NET [209.128.104.244])
	by svr1.postgresql.org (Postfix) with ESMTP id B46FB533E3
	for <pgsql-sql@postgresql.org>; Mon, 18 Apr 2005 01:43:39 -0300 (ADT)
Received: from dinesh by secf.com (MDaemon.PRO.v7.2.2.R)
	with ESMTP id md50000650810.msg
	for <pgsql-sql@postgresql.org>; Sun, 17 Apr 2005 21:43:44 -0700
Reply-To: <dpandey@secf.com>
From: "Dinesh Pandey" <dpandey@secf.com>
To: "'Michael Fuhr'" <mike@fuhr.org>
Cc: "'PostgreSQL'" <pgsql-sql@postgresql.org>
Subject: Re: Error handling in pltcl
Date: Mon, 18 Apr 2005 10:10:20 +0530
Organization: Second Foundation India P Ltd.
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
X-Mailer: Microsoft Office Outlook, Build 11.0.5510
Thread-Index: AcVDz75NfP3OJvmPTp20nofDCaNMcQAAGq+Q
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
In-Reply-To: <20050418043232.GA94872@winnie.fuhr.org>
X-Authenticated-Sender: dpandey@secf.com
X-Spam-Processed: mail.secf.com, Sun, 17 Apr 2005 21:43:44 -0700
	(not processed: message from valid local sender)
X-MDRemoteIP: 220.227.32.11
X-Return-Path: dpandey@secf.com
X-MDaemon-Deliver-To: pgsql-sql@postgresql.org
X-MDAV-Processed: mail.secf.com, Sun, 17 Apr 2005 21:43:56 -0700
Message-Id: <20050418044339.B46FB533E3@svr1.postgresql.org>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=1.392 tagged_above=0 required=5 tests=AWL,
	FORGED_RCVD_HELO, MSGID_FROM_MTA_ID
X-Spam-Level: *
X-Archive-Number: 200504/239
X-Sequence-Number: 21132

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


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