Thread: Implementation of SQLCODE and SQLERRM variables for PL/pgSQL
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
Attachment
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
Attachment
- 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
Attachment
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: > > 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 19073
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. 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 19073
Pavel 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 19073
Removed. 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