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