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