[HACKERS] 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: 00 Sucessful completion
NOTICE: P0001 last exception
trap_exceptions
-
(1 row)
DROP FUNCTION
Regards,
Pavel Stehule
--
-- 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: 00 Sucessfull completation
NOTICE: P0001 last exception
trap_exceptions
-
(1 row)
DROP FUNCTION
357a358,360
int sqlcode_varno;
int sqlerrm_varno;
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(00));
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 */
chartbuf[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));
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);
Re: [HACKERS] Implementation of SQLCODE and SQLERRM variables for PL/pgSQL
Pavel Stehule [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Implementation of SQLCODE and SQLERRM variables for
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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
