Exception ERROR Code
Hi ,
I am looking for the way to get the error code
corresponding to the exception in plpgsql.
Can any body guide me.
Thanks,
Asif Ali
__________________________________
Celebrate Yahoo!'s 10th Birthday!
Yahoo! Netrospective: 100 Moments of the Web
http://birthday.yahoo.com/netrospective/
On Sat, Mar 05, 2005 at 06:03:20AM -0800, Ali Baba wrote:
I am looking for the way to get the error code
corresponding to the exception in plpgsql.
What exception? Can you describe what you're trying to do? Are
you using the EXCEPTION clause that's available in the latest
release, or are you hacking PL/pgSQL itself?
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
Hello,
It's no possible now. But I prepared small patch which implemented
variables sqlcode and sqlerrm for plpgsql. I can send it tomorrow.
regards
Pavel Stehule
On Sat, 5 Mar 2005, Ali Baba wrote:
Show quoted text
Hi ,
I am looking for the way to get the error code
corresponding to the exception in plpgsql.
Can any body guide me.Thanks,
Asif Ali__________________________________
Celebrate Yahoo!'s 10th Birthday!
Yahoo! Netrospective: 100 Moments of the Web
http://birthday.yahoo.com/netrospective/---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Hello
This is my second patch, than please will be tolerant :-). For one my
project I miss information about exception when I use EXCEPTION WITH
OTHERS THEN. I found two Oracle's varaibles SQLCODE and SQLERRM which
carry this information. With patch you can:
--
-- Test of built variables SQLERRM and SQLCODE
--
create or replace function trap_exceptions() returns void as $_$
begin
begin
raise exception 'first exception';
exception when others then
raise notice '% %', SQLCODE, SQLERRM;
end;
raise notice '% %', SQLCODE, SQLERRM;
begin
raise exception 'last exception';
exception when others then
raise notice '% %', SQLCODE, SQLERRM;
end;
return;
end; $_$ language plpgsql;
select trap_exceptions();
drop function trap_exceptions();
CREATE FUNCTION
NOTICE: P0001 first exception
NOTICE: 000000 Sucessful completion
NOTICE: P0001 last exception
trap_exceptions
-----------------
(1 row)
DROP FUNCTION
Regards,
Pavel Stehule
Attachments:
plpgsql.h.difftext/plain; charset=US-ASCII; name=plpgsql.h.diffDownload
357a358,360
> int sqlcode_varno;
> int sqlerrm_varno;
>
pl_exec.c.difftext/plain; charset=US-ASCII; name=pl_exec.c.diffDownload
826a827,842
> /* INICIALIZACE fooi a foot */
> PLpgSQL_var *var;
>
> var = (PLpgSQL_var *) (estate->datums[block->sqlcode_varno]);
> var->isnull = false;
> var->freeval = false;
> var->value = DirectFunctionCall1(textin, CStringGetDatum("000000"));
>
> var = (PLpgSQL_var *) (estate->datums[block->sqlerrm_varno]);
>
> var->isnull = false;
> var->freeval = false;
> var->value = DirectFunctionCall1(textin, CStringGetDatum("Sucessful completion"));
>
>
>
931a948,966
> /* unpack MAKE_SQLSTATE code */
> char tbuf[12];
> int ssval;
> int i;
>
> ssval = edata->sqlerrcode;
> for (i = 0; i < 5; i++)
> {
> tbuf[i] = PGUNSIXBIT(ssval);
> ssval >>= 6;
> }
> tbuf[i] = '\0';
> var = (PLpgSQL_var *) (estate->datums[block->sqlcode_varno]);
> var->value = DirectFunctionCall1(textin, CStringGetDatum(tbuf));
>
> var = (PLpgSQL_var *) (estate->datums[block->sqlerrm_varno]);
> var->value = DirectFunctionCall1(textin, CStringGetDatum(edata->message));
>
>
gram.y.difftext/plain; charset=US-ASCII; name=gram.y.diffDownload
88a89,93
> struct
> {
> int sqlcode_varno;
> int sqlerrm_varno;
> } fict_vars;
104a110
> %type <fict_vars> fict_vars_sect
251c257
< pl_block : decl_sect K_BEGIN lno proc_sect exception_sect K_END
---
> pl_block : decl_sect fict_vars_sect K_BEGIN lno proc_sect exception_sect K_END
259c265
< new->lineno = $3;
---
> new->lineno = $4;
263,264c269,272
< new->body = $4;
< new->exceptions = $5;
---
> new->body = $5;
> new->exceptions = $6;
> new->sqlcode_varno = $2.sqlcode_varno;
> new->sqlerrm_varno = $2.sqlerrm_varno;
271a280,291
> fict_vars_sect :
> {
> plpgsql_ns_setlocal(false);
> PLpgSQL_variable *var;
> var = plpgsql_build_variable(strdup("sqlcode"), 0,
> plpgsql_build_datatype(TEXTOID, -1), true);
> $$.sqlcode_varno = var->dno;
> var = plpgsql_build_variable(strdup("sqlerrm"), 0,
> plpgsql_build_datatype(TEXTOID, -1), true);
> $$.sqlerrm_varno = var->dno;
> plpgsql_add_initdatums(NULL);
> }
693a714
>
Pavel Stehule <stehule@kix.fsv.cvut.cz> writes:
This is my second patch, than please will be tolerant :-). For one my
project I miss information about exception when I use EXCEPTION WITH
OTHERS THEN. I found two Oracle's varaibles SQLCODE and SQLERRM which
carry this information.
I think we discussed this last year and decided that it would be a bad
idea to use those names because Oracle's use of them is not exactly
compatible with our error codes and messages. SQLCODE in particular is
not compatible at all --- it's an integer in Oracle, isn't it?
IIRC we had put off solving this problem until we decided what to do
with RAISE. There really needs to be some changes in RAISE to allow it
to raise a specific error code rather than always P0001, but exactly
what is still undecided.
Some other problems with your patch: no documentation, and not in
diff -c format. Plain diff patches are never acceptable because
it's too risky to apply them against files that might have changed
since you started working with them. Also, it's much easier to
deal with one patch than with a separate diff for each file.
(diff -c -r between an original and a modified directory is one
good way to produce a useful patch.)
regards, tom lane
I think we discussed this last year and decided that it would be a bad
idea to use those names because Oracle's use of them is not exactly
compatible with our error codes and messages. SQLCODE in particular is
not compatible at all --- it's an integer in Oracle, isn't it?
There is more incompatibilities to Oracle. SQLERRM is function on Oracle,
only if you use it without parametr, returns current message error.
SQLCODE is really integer. But it's only names. There is no problem change
it.
IIRC we had put off solving this problem until we decided what to do
with RAISE. There really needs to be some changes in RAISE to allow it
to raise a specific error code rather than always P0001, but exactly
what is still undecided.
I didn't know it. But for my work is SQLERRM more important. I have more
constraints on tables and I need detect which which constraints raise
exception. The possibility EXCEPTION WITH OTHERS is nice, but not too much
usefull because I have not possibility get some informations about except.
Some other problems with your patch: no documentation, and not in
diff -c format. Plain diff patches are never acceptable because
it's too risky to apply them against files that might have changed
since you started working with them. Also, it's much easier to
deal with one patch than with a separate diff for each file.
(diff -c -r between an original and a modified directory is one
good way to produce a useful patch.)
I am not sure, I able create documentation - my english is poor. I will
change diff's format and send patch again.
Thank you
Pavel
Hello,
I used different format now. Documentation:
This patch is implementation of variables SQLERRM and SQLCODE for plpgsql
language. Variable SQLCODE 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
'00000' for SQLCODE and 'Sucessful completion' for SQLERRM. Some example
of using is in file test.sql.
Regards
Pavel Stehule
Attachments:
sqlcode.difftext/plain; charset=US-ASCII; name=sqlcode.diffDownload
diff -c -r src.old/gram.y src/gram.y
*** src.old/gram.y 2005-02-22 08:18:24.000000000 +0100
--- src/gram.y 2005-03-07 10:05:29.286336064 +0100
***************
*** 80,85 ****
--- 80,90 ----
int n_initvars;
int *initvarnos;
} declhdr;
+ struct
+ {
+ int sqlcode_varno;
+ int sqlerrm_varno;
+ } fict_vars;
List *list;
PLpgSQL_type *dtype;
PLpgSQL_datum *scalar; /* a VAR, RECFIELD, or TRIGARG */
***************
*** 95,101 ****
PLpgSQL_nsitem *nsitem;
PLpgSQL_diag_item *diagitem;
}
!
%type <declhdr> decl_sect
%type <varname> decl_varname
%type <str> decl_renname
--- 100,106 ----
PLpgSQL_nsitem *nsitem;
PLpgSQL_diag_item *diagitem;
}
! %type <fict_vars> fict_vars_sect
%type <declhdr> decl_sect
%type <varname> decl_varname
%type <str> decl_renname
***************
*** 244,268 ****
| ';'
;
! pl_block : decl_sect K_BEGIN lno proc_sect exception_sect K_END
{
PLpgSQL_stmt_block *new;
new = palloc0(sizeof(PLpgSQL_stmt_block));
new->cmd_type = PLPGSQL_STMT_BLOCK;
! new->lineno = $3;
new->label = $1.label;
new->n_initvars = $1.n_initvars;
new->initvarnos = $1.initvarnos;
! new->body = $4;
! new->exceptions = $5;
plpgsql_ns_pop();
$$ = (PLpgSQL_stmt *)new;
}
;
decl_sect : opt_label
--- 249,288 ----
| ';'
;
! pl_block : decl_sect fict_vars_sect K_BEGIN lno proc_sect exception_sect K_END
{
PLpgSQL_stmt_block *new;
new = palloc0(sizeof(PLpgSQL_stmt_block));
new->cmd_type = PLPGSQL_STMT_BLOCK;
! new->lineno = $4;
new->label = $1.label;
new->n_initvars = $1.n_initvars;
new->initvarnos = $1.initvarnos;
! new->body = $5;
! new->exceptions = $6;
!
! new->sqlcode_varno = $2.sqlcode_varno;
! new->sqlerrm_varno = $2.sqlerrm_varno;
plpgsql_ns_pop();
$$ = (PLpgSQL_stmt *)new;
}
;
+ fict_vars_sect :
+ {
+ plpgsql_ns_setlocal(false);
+ PLpgSQL_variable *var;
+ var = plpgsql_build_variable(strdup("sqlcode"), 0,
+ plpgsql_build_datatype(TEXTOID, -1), true);
+ $$.sqlcode_varno = var->dno;
+ var = plpgsql_build_variable(strdup("sqlerrm"), 0,
+ plpgsql_build_datatype(TEXTOID, -1), true);
+ $$.sqlerrm_varno = var->dno;
+ plpgsql_add_initdatums(NULL);
+ };
decl_sect : opt_label
diff -c -r src.old/pl_exec.c src/pl_exec.c
*** src.old/pl_exec.c 2005-02-24 02:11:40.000000000 +0100
--- src/pl_exec.c 2005-03-07 09:53:52.630243888 +0100
***************
*** 809,814 ****
--- 809,828 ----
int i;
int n;
+ /* setup SQLCODE and SQLERRM */
+ PLpgSQL_var *var;
+
+ var = (PLpgSQL_var *) (estate->datums[block->sqlcode_varno]);
+ var->isnull = false;
+ var->freeval = false;
+ var->value = DirectFunctionCall1(textin, CStringGetDatum("00000"));
+
+ var = (PLpgSQL_var *) (estate->datums[block->sqlerrm_varno]);
+ var->isnull = false;
+ var->freeval = false;
+ var->value = DirectFunctionCall1(textin, CStringGetDatum("Sucessful completion"));
+
+
/*
* First initialize all variables declared in this block
*/
***************
*** 918,923 ****
--- 932,957 ----
MemoryContextSwitchTo(oldcontext);
CurrentResourceOwner = oldowner;
+
+ /* set SQLCODE and SQLERRM variables */
+ /* unpack MAKE_SQLSTATE code */
+ char tbuf[12];
+ int ssval;
+ int i;
+
+ ssval = edata->sqlerrcode;
+ for (i = 0; i < 5; i++)
+ {
+ tbuf[i] = PGUNSIXBIT(ssval);
+ ssval >>= 6;
+ }
+ tbuf[i] = '\0';
+ var = (PLpgSQL_var *) (estate->datums[block->sqlcode_varno]);
+ var->value = DirectFunctionCall1(textin, CStringGetDatum(tbuf));
+
+ var = (PLpgSQL_var *) (estate->datums[block->sqlerrm_varno]);
+ var->value = DirectFunctionCall1(textin, CStringGetDatum(edata->message));
+
/*
* If AtEOSubXact_SPI() popped any SPI context of the subxact,
* it will have left us in a disconnected state. We need this
diff -c -r src.old/plpgsql.h src/plpgsql.h
*** src.old/plpgsql.h 2005-02-22 08:18:24.000000000 +0100
--- src/plpgsql.h 2005-03-07 09:39:25.613050536 +0100
***************
*** 339,344 ****
--- 339,346 ----
List *exceptions; /* List of WHEN clauses */
int n_initvars;
int *initvarnos;
+ int sqlcode_varno;
+ int sqlerrm_varno;
} PLpgSQL_stmt_block;
- You should write some regression tests for this functionality
- You should update the documentation
- Is there a reason why you've made the type of SQLCODE `text', rather
than integer?
Pavel Stehule wrote:
+ fict_vars_sect : + { + plpgsql_ns_setlocal(false); + PLpgSQL_variable *var; + var = plpgsql_build_variable(strdup("sqlcode"), 0, + plpgsql_build_datatype(TEXTOID, -1), true); + $$.sqlcode_varno = var->dno; + var = plpgsql_build_variable(strdup("sqlerrm"), 0, + plpgsql_build_datatype(TEXTOID, -1), true);
This shouldn't be strdup'ing its first argument (and even if it needed
to make a copy, it should use pstrdup). Also, my personal preference
would be to implement this without creating a new production (i.e. just
include it inline in the body of the pl_block production).
*** src.old/pl_exec.c 2005-02-24 02:11:40.000000000 +0100 --- src/pl_exec.c 2005-03-07 09:53:52.630243888 +0100 *************** *** 809,814 **** --- 809,828 ---- int i; int n;+ /* setup SQLCODE and SQLERRM */ + PLpgSQL_var *var; + + var = (PLpgSQL_var *) (estate->datums[block->sqlcode_varno]); + var->isnull = false; + var->freeval = false; + var->value = DirectFunctionCall1(textin, CStringGetDatum("00000")); + + var = (PLpgSQL_var *) (estate->datums[block->sqlerrm_varno]); + var->isnull = false; + var->freeval = false; + var->value = DirectFunctionCall1(textin, CStringGetDatum("Sucessful completion"));
`freeval' should be true, no? (Not sure it actually matters, but text is
certainly not pass-by-value).
*************** *** 918,923 **** --- 932,957 ----
[...]
+ var = (PLpgSQL_var *) (estate->datums[block->sqlcode_varno]); + var->value = DirectFunctionCall1(textin, CStringGetDatum(tbuf)); + + var = (PLpgSQL_var *) (estate->datums[block->sqlerrm_varno]); + var->value = DirectFunctionCall1(textin, CStringGetDatum(edata->message));
You should probably pfree() the old values before replacing them.
-Neil
Neil Conway <neilc@samurai.com> writes:
- Is there a reason why you've made the type of SQLCODE `text', rather
than integer?
The value isn't an integer ... which gets back to my point that this is
not compatible with Oracle's idea of SQLCODE and therefore we should *not*
use that name for it.
BTW: the patch has some memory-leak problems, I believe, because it is
studiously not following the var->freeval protocol. Now that I look,
it appears to be copied-and-pasted from some existing code that also
gets this wrong :-(
regards, tom lane
Hello,
I changed code by your and Neil's notes. The name SQLCODE isn't well,
better is SQLSTATE. It's very similar DB2 variable. I am not sure, so my
solutions is best. Propably not. It's only particular solution for
plpgsql. The files plpgsql.sql and plpgsql.out are diffs for regression
tests.
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
'00000' for SQLSTATE and 'Sucessful completion' for SQLERRM.
Regards
Pavel Stehule
Attachments:
plpgsql.sqltext/plain; CHARSET=US-ASCII; NAME=plpgsql.sqlDownload
*** test.old/plpgsql.sql 2005-02-22 08:18:27.000000000 +0100
--- test/plpgsql.sql 2005-03-08 09:58:23.419281208 +0100
***************
*** 1917,1920 ****
create function void_return_expr() returns void as $$
begin
return 5;
! end;$$ language plpgsql;
--- 1917,1944 ----
create function void_return_expr() returns void as $$
begin
return 5;
! end;$$ language plpgsql;
!
! --
! -- Test of built variables SQLERRM and SQLSTATE
! --
!
! create or replace function trap_exceptions() returns void as $_$
! begin
! begin
! raise exception 'first exception';
! exception when others then
! raise notice '% %', SQLSTATE, SQLERRM;
! end;
! raise notice '% %', SQLSTATE, SQLERRM;
! begin
! raise exception 'last exception';
! exception when others then
! raise notice '% %', SQLSTATE, SQLERRM;
! end;
! return;
! end; $_$ language plpgsql;
!
! select trap_exceptions();
!
!
plpgsql.outtext/plain; CHARSET=US-ASCII; NAME=plpgsql.outDownload
*** test.old/plpgsql.out 2005-02-22 08:18:25.000000000 +0100
--- test/plpgsql.out 2005-03-08 09:56:58.272225528 +0100
***************
*** 2242,2244 ****
--- 2242,2252 ----
ERROR: function returning void cannot specify RETURN expression at or near "5" at character 72
LINE 3: return 5;
^
+ CREATE FUNCTION
+ psql:plpgsql.sql:1942: NOTICE: P0001 first exception
+ psql:plpgsql.sql:1942: NOTICE: 000000 Sucessful completion
+ psql:plpgsql.sql:1942: NOTICE: P0001 last exception
+ trap_exceptions
+ -----------------
+
+ (1 row)
sqlstate.difftext/plain; CHARSET=US-ASCII; NAME=sqlstate.diffDownload
diff -c -r src.old/gram.y src/gram.y
*** src.old/gram.y 2005-02-22 08:18:24.000000000 +0100
--- src/gram.y 2005-03-08 09:22:20.886036232 +0100
***************
*** 80,85 ****
--- 80,90 ----
int n_initvars;
int *initvarnos;
} declhdr;
+ struct
+ {
+ int sqlstate_varno;
+ int sqlerrm_varno;
+ } fict_vars;
List *list;
PLpgSQL_type *dtype;
PLpgSQL_datum *scalar; /* a VAR, RECFIELD, or TRIGARG */
***************
*** 95,101 ****
PLpgSQL_nsitem *nsitem;
PLpgSQL_diag_item *diagitem;
}
!
%type <declhdr> decl_sect
%type <varname> decl_varname
%type <str> decl_renname
--- 100,106 ----
PLpgSQL_nsitem *nsitem;
PLpgSQL_diag_item *diagitem;
}
! %type <fict_vars> fict_vars_sect
%type <declhdr> decl_sect
%type <varname> decl_varname
%type <str> decl_renname
***************
*** 244,268 ****
| ';'
;
! pl_block : decl_sect K_BEGIN lno proc_sect exception_sect K_END
{
PLpgSQL_stmt_block *new;
new = palloc0(sizeof(PLpgSQL_stmt_block));
new->cmd_type = PLPGSQL_STMT_BLOCK;
! new->lineno = $3;
new->label = $1.label;
new->n_initvars = $1.n_initvars;
new->initvarnos = $1.initvarnos;
! new->body = $4;
! new->exceptions = $5;
plpgsql_ns_pop();
$$ = (PLpgSQL_stmt *)new;
}
;
decl_sect : opt_label
--- 249,288 ----
| ';'
;
! pl_block : decl_sect fict_vars_sect K_BEGIN lno proc_sect exception_sect K_END
{
PLpgSQL_stmt_block *new;
new = palloc0(sizeof(PLpgSQL_stmt_block));
new->cmd_type = PLPGSQL_STMT_BLOCK;
! new->lineno = $4;
new->label = $1.label;
new->n_initvars = $1.n_initvars;
new->initvarnos = $1.initvarnos;
! new->body = $5;
! new->exceptions = $6;
!
! new->sqlstate_varno = $2.sqlstate_varno;
! new->sqlerrm_varno = $2.sqlerrm_varno;
plpgsql_ns_pop();
$$ = (PLpgSQL_stmt *)new;
}
;
+ fict_vars_sect :
+ {
+ plpgsql_ns_setlocal(false);
+ PLpgSQL_variable *var;
+ var = plpgsql_build_variable("sqlstate", 0,
+ plpgsql_build_datatype(TEXTOID, -1), true);
+ $$.sqlstate_varno = var->dno;
+ var = plpgsql_build_variable("sqlerrm", 0,
+ plpgsql_build_datatype(TEXTOID, -1), true);
+ $$.sqlerrm_varno = var->dno;
+ plpgsql_add_initdatums(NULL);
+ };
decl_sect : opt_label
diff -c -r src.old/pl_exec.c src/pl_exec.c
*** src.old/pl_exec.c 2005-02-24 02:11:40.000000000 +0100
--- src/pl_exec.c 2005-03-08 09:40:16.537512352 +0100
***************
*** 181,186 ****
--- 181,187 ----
static void exec_init_tuple_store(PLpgSQL_execstate *estate);
static bool compatible_tupdesc(TupleDesc td1, TupleDesc td2);
static void exec_set_found(PLpgSQL_execstate *estate, bool state);
+ static char *unpack_sql_state(int ssval);
/* ----------
***************
*** 809,814 ****
--- 810,829 ----
int i;
int n;
+ /* setup SQLSTATE and SQLERRM */
+ PLpgSQL_var *var;
+
+ var = (PLpgSQL_var *) (estate->datums[block->sqlstate_varno]);
+ var->isnull = false;
+ var->freeval = true;
+ var->value = DirectFunctionCall1(textin, CStringGetDatum("00000"));
+
+ var = (PLpgSQL_var *) (estate->datums[block->sqlerrm_varno]);
+ var->isnull = false;
+ var->freeval = true;
+ var->value = DirectFunctionCall1(textin, CStringGetDatum("Sucessful completion"));
+
+
/*
* First initialize all variables declared in this block
*/
***************
*** 918,923 ****
--- 933,949 ----
MemoryContextSwitchTo(oldcontext);
CurrentResourceOwner = oldowner;
+
+ /* set SQLSTATE and SQLERRM variables */
+
+ var = (PLpgSQL_var *) (estate->datums[block->sqlstate_varno]);
+ pfree((void *) (var->value));
+ var->value = DirectFunctionCall1(textin, CStringGetDatum(unpack_sql_state(edata->sqlerrcode)));
+
+ var = (PLpgSQL_var *) (estate->datums[block->sqlerrm_varno]);
+ pfree((void *) (var->value));
+ var->value = DirectFunctionCall1(textin, CStringGetDatum(edata->message));
+
/*
* If AtEOSubXact_SPI() popped any SPI context of the subxact,
* it will have left us in a disconnected state. We need this
***************
*** 4319,4321 ****
--- 4345,4368 ----
FreeExecutorState(simple_eval_estate);
simple_eval_estate = NULL;
}
+
+ /*
+ * unpack MAKE_SQLSTATE code
+ * This code is redundand backend/utils/error/elog.c. I din't
+ * wont modify different part than plpgsql
+ */
+
+ static char *
+ unpack_sql_state(int ssval)
+ {
+ static char tbuf[12];
+ int i;
+
+ for (i = 0; i < 5; i++)
+ {
+ tbuf[i] = PGUNSIXBIT(ssval);
+ ssval >>= 6;
+ }
+ tbuf[i] = '\0';
+ return tbuf;
+ }
diff -c -r src.old/plpgsql.h src/plpgsql.h
*** src.old/plpgsql.h 2005-02-22 08:18:24.000000000 +0100
--- src/plpgsql.h 2005-03-08 08:59:46.877876760 +0100
***************
*** 339,344 ****
--- 339,346 ----
List *exceptions; /* List of WHEN clauses */
int n_initvars;
int *initvarnos;
+ int sqlstate_varno;
+ int sqlerrm_varno;
} PLpgSQL_stmt_block;
Guys, is this patch ready for application? I think so, but am not 100%
sure.
---------------------------------------------------------------------------
Pavel Stehule wrote:
Hello,
I changed code by your and Neil's notes. The name SQLCODE isn't well,
better is SQLSTATE. It's very similar DB2 variable. I am not sure, so my
solutions is best. Propably not. It's only particular solution for
plpgsql. The files plpgsql.sql and plpgsql.out are diffs for regression
tests.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
'00000' for SQLSTATE and 'Sucessful completion' for SQLERRM.Regards
Pavel Stehule
Content-Description:
[ Attachment, skipping... ]
Content-Description:
[ Attachment, skipping... ]
Content-Description:
[ Attachment, skipping... ]
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Hello All,
Where can I find patch for SQLERRM/SQLSTATE variable implementation?.
I am using Postgres 8.0.1.
Its very urgent.... because I have an assignment of Porting Oracle's PL?SQL
into PL/pgSQL.
Thanks
Dinesh Pandey
-----Original Message-----
From: pgsql-patches-owner@postgresql.org
[mailto:pgsql-patches-owner@postgresql.org] On Behalf Of Bruce Momjian
Sent: Tuesday, April 19, 2005 9:29 AM
To: Pavel Stehule
Cc: Tom Lane; neilc@samurai.com; pgsql-patches@postgresql.org
Subject: Re: [PATCHES] [HACKERS] Implementation of SQLCODE and SQLERRM
variables
Guys, is this patch ready for application? I think so, but am not 100%
sure.
---------------------------------------------------------------------------
Pavel Stehule wrote:
Hello,
I changed code by your and Neil's notes. The name SQLCODE isn't well,
better is SQLSTATE. It's very similar DB2 variable. I am not sure, so my
solutions is best. Propably not. It's only particular solution for
plpgsql. The files plpgsql.sql and plpgsql.out are diffs for regression
tests.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
'00000' for SQLSTATE and 'Sucessful completion' for SQLERRM.Regards
Pavel Stehule
Content-Description:
[ Attachment, skipping... ]
Content-Description:
[ Attachment, skipping... ]
Content-Description:
[ Attachment, skipping... ]
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
Bruce Momjian <pgman@candle.pha.pa.us> writes:
Guys, is this patch ready for application? I think so, but am not 100%
sure.
I haven't read the code yet, but in any case I still object to choosing
Oracle-like names for Oracle-incompatible functionality. We need to
settle on better names.
regards, tom lane
Hello,
I am not sure, I corrected memory problems, I think, but there are some
changes in plpgsql code. I'l update patch today.
Regards
Pavel Stehule
On Mon, 18 Apr 2005, Bruce Momjian wrote:
Show quoted text
Guys, is this patch ready for application? I think so, but am not 100%
sure.---------------------------------------------------------------------------
Pavel Stehule wrote:
Hello,
I changed code by your and Neil's notes. The name SQLCODE isn't well,
better is SQLSTATE. It's very similar DB2 variable. I am not sure, so my
solutions is best. Propably not. It's only particular solution for
plpgsql. The files plpgsql.sql and plpgsql.out are diffs for regression
tests.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
'00000' for SQLSTATE and 'Sucessful completion' for SQLERRM.Regards
Pavel Stehule
Content-Description:
[ Attachment, skipping... ]
Content-Description:
[ Attachment, skipping... ]
Content-Description:
[ Attachment, skipping... ]
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
On Tue, 19 Apr 2005, Tom Lane wrote:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
Guys, is this patch ready for application? I think so, but am not 100%
sure.I haven't read the code yet, but in any case I still object to choosing
Oracle-like names for Oracle-incompatible functionality. We need to
settle on better names.
I changed names on SQLSTATE and SQLERRM. The behave our SQLSTATE and db2
SQLSTATE is very similar. But Oracle support SQLSTATE too (in ANSI mode).
Only Oracle support SQLERRM.
http://developer.mimer.com/howto/howto_25.htm
SQLSTATE, a 5-character return value specified by the SQL standard,
replaced SQLCODE years ago (SQL-92?). Perhaps both Oracle and DB2/2
have support for it.
But I didn't find good informations about error trapping in ANSI SQL. All
informations are about Oracle or DB2 :-(.
Best Regards
Pavel Stehule
Pavel Stehule wrote:
Hello,
I am not sure, I corrected memory problems, I think, but there are some
changes in plpgsql code. I'l update patch today.
OK, great thanks.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073Tom Lane wrote:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
Guys, is this patch ready for application? I think so, but am not 100%
sure.I haven't read the code yet, but in any case I still object to choosing
Oracle-like names for Oracle-incompatible functionality. We need to
settle on better names.
OK, are the values returned not the same as the Oracle values? If they
aren't, you are right, we need another name.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073Pavel Stehule wrote:
On Tue, 19 Apr 2005, Tom Lane wrote:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
Guys, is this patch ready for application? I think so, but am not 100%
sure.I haven't read the code yet, but in any case I still object to choosing
Oracle-like names for Oracle-incompatible functionality. We need to
settle on better names.I changed names on SQLSTATE and SQLERRM. The behave our SQLSTATE and db2
SQLSTATE is very similar. But Oracle support SQLSTATE too (in ANSI mode).
Only Oracle support SQLERRM.http://developer.mimer.com/howto/howto_25.htm
SQLSTATE, a 5-character return value specified by the SQL standard,
replaced SQLCODE years ago (SQL-92?). Perhaps both Oracle and DB2/2
have support for it.But I didn't find good informations about error trapping in ANSI SQL. All
informations are about Oracle or DB2 :-(.
Oh, it sounds like we are OK then with the names you used.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073Removed. New version coming.
---------------------------------------------------------------------------
Pavel Stehule wrote:
Hello,
I changed code by your and Neil's notes. The name SQLCODE isn't well,
better is SQLSTATE. It's very similar DB2 variable. I am not sure, so my
solutions is best. Propably not. It's only particular solution for
plpgsql. The files plpgsql.sql and plpgsql.out are diffs for regression
tests.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
'00000' for SQLSTATE and 'Sucessful completion' for SQLERRM.Regards
Pavel Stehule
Content-Description:
[ Attachment, skipping... ]
Content-Description:
[ Attachment, skipping... ]
Content-Description:
[ Attachment, skipping... ]
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073