[PATCHES] unsubscribe

[Dinesh Pandey]

unsubscribe

Re: [PATCHES] Implementation of SQLSTATE and SQLERRM variables

[Dinesh Pandey]

Is this implementation is available in postgres 8.0.2 ? Or which version of
Postgres?


Thanks
Dinesh Pandey


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Pavel Stehule
Sent: Thursday, May 26, 2005 12:47 PM
To: Neil Conway
Cc: Bruce Momjian; pgsql-patches@postgresql.org; 'Tom Lane'
Subject: Re: [PATCHES] Implementation of SQLSTATE and SQLERRM variables

 I think the docs need more than just these variables are set when an 
 exception is raised.
 
 The patch current resets SQLSTATE and SQLERRM whenever a new block is 
 entered. So:
 
 create function trap_exceptions() returns void as $_$ begin
 begin
   raise exception 'first exception';
 exception when others then
   raise notice '% %', SQLSTATE, SQLERRM;
   begin
 raise notice '% %', SQLSTATE, SQLERRM;
   end;
 end;
 return;
 end; $_$ language plpgsql;
 
 The second RAISE will report successful completion. Is this the 
 behavior we want?

True, its foggy behave. Only if block contains exception than variables
SQLSTATE and SQLERRM are created. 

 
 Is SQLERRM the best name for that variable? It seems a little obscure to
me.
 

This name use Oracle in PL/SQL. ESQL Oracle and DB2 too. I think so is
better use well knowed names for it than creating own. There is one argument
for it. PL/pgSQL is language very similar Oracle PL/SQL. PL/SQL know
SQLERRM, SQLCODE and SQLSTATE. An people which know PL/SQL will like use
this names in Pg.

Pavel Stehule


 -Neil
 
 ---(end of 
 broadcast)---
 TIP 5: Have you checked our extensive FAQ?
 
http://www.postgresql.org/docs/faq
 


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



---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [PATCHES] Implementation of SQLSTATE and SQLERRM variables

[Dinesh Pandey]

Hi,

I have Postgres 8.0.1 on Solaris 9 Sparc machine running in
usr/local/pgsql directory.

I am trying to apply  sqlstate.diff patch for SQLERRM and SQLSTATE for
plpgsql.

1. I have copied sqlstate.diff in
postgresql-8.0.1.old/src/pl/plpgsql/src directory.
2. Executing command:
patch -p4  sqlstate.diff

Now it is asking for  File to patch:, here I am giving gram.y (I don't
know what it should be). After this it is giving the following error.

bash-2.03# patch -p4  sqlstate.diff
  Looks like a context diff to me...
File to patch: gram.y
Hunk #3 failed at line 244.
Malformed patch at line 93:
patch: Line must begin with '+ ', '  ', or '! '.
bash-2.03# 
bash-2.03#


Kindly let me know what's wrong?

Please help me how to apply this patch.


Thanks
Dinesh Pandey

-Original Message-
Subject: [PATCHES] Implementation of SQLSTATE and SQLERRM variables

Hello,

I updated patch to last changes plpgsql code. Patch contains changes for 
gram.y, pl_exec.c, plpgsql.h, regress/sql/plpgsql.sql and 
regress/output/plpgsql.out. I can't to write documenation, my english is 
terrible.

This patch is implementation of variables SQLERRM and SQLSTATE for plpgsql 
language. Variable SQLSTATE contains five chars PostgreSQL Error Code, 
SQLERRM contains relevant message last catched exception. All variables 
are attached to plpgsql_block and have local scope. Default values are 
'0' for SQLSTATE and 'Sucessful completion' for SQLERRM. 

Best regards
Pavel Stehule


sqlstate.diff
Description: Binary data

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

FW: [PATCHES] Implementation of SQLSTATE and SQLERRM variables

[Dinesh Pandey]

I have Postgres 8.0.1 on Solaris 9 Sparc machine running in
usr/local/pgsql directory and my source is in
postgresql-8.0.1/src/pl/plpgsql/src directory.

In sqlstate.diff file it is looking 
 diff -c -r pgsql.old/src/pl/plpgsql/src/gram.y
pgsql/src/pl/plpgsql/src/gram.y 
checking diff between 2 directories pgsql.old/src and pgsql/src.

Should I have to also copy Postgres source in 2 directories as
postgresql-8.0.1/src and postgresql-8.0.1.old/src If it is, so where
to run this patch?

Dinesh
-

Hi,

I have Postgres 8.0.1 on Solaris 9 Sparc machine running in
usr/local/pgsql directory  and my source is in
postgresql-8.0.1/src/pl/plpgsql/src directory.

I am trying to apply sqlstate.diff patch for SQLERRM and SQLSTATE for
plpgsql. 

1. I have copied sqlstate.diff in postgresql-8.0.1/src/pl/plpgsql/src
directory.
2. Executing command:
patch -p4  sqlstate.diff

Now it is asking for  File to patch:, here I am giving gram.y (I don't
know what it should be). After this it is giving the following error.

bash-2.03# patch -p4  sqlstate.diff
  Looks like a context diff to me...
File to patch: gram.y
Hunk #3 failed at line 244.
Malformed patch at line 93:
patch: Line must begin with '+ ', '  ', or '! '.
bash-2.03# 
bash-2.03#


Kindly let me know what's wrong?

Please help me how to apply this patch.


Thanks
Dinesh Pandey

-Original Message-
Subject: [PATCHES] Implementation of SQLSTATE and SQLERRM variables

Hello,

I updated patch to last changes plpgsql code. Patch contains changes for 
gram.y, pl_exec.c, plpgsql.h, regress/sql/plpgsql.sql and 
regress/output/plpgsql.out. I can't to write documenation, my english is 
terrible.

This patch is implementation of variables SQLERRM and SQLSTATE for plpgsql 
language. Variable SQLSTATE contains five chars PostgreSQL Error Code, 
SQLERRM contains relevant message last catched exception. All variables 
are attached to plpgsql_block and have local scope. Default values are 
'0' for SQLSTATE and 'Sucessful completion' for SQLERRM. 

Best regards
Pavel Stehule


sqlstate.diff
Description: Binary data

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

[PATCHES] How to include EXCEPTION handling block in PL/TCL function.

[Dinesh Pandey]

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

[PATCHES] Exception handling: Oracle's SQLERRM keyword option?

[Dinesh Pandey]

Hi,



I have installed PostgreSQL 8.0.1 on Solaris 9.



I am porting my database from Oracle 9i to PostgreSQL. I
am using PL/pgSQL language.



In Oracle we can get errormessagefrom SQLERRM
keyword and insert messages into log table. 



How
can I get errormessage/error codein PostgreSQL after an EXCEPTION
or RAISE EXCEPTION occurs in EXCEPTION block??



Pls help me or send me some example.





Thanks

Dinesh Pandey