Re: pgsql: Add PL/pgSQL SQLSTATE and SQLERRM support

>     BEGIN
>         -- do something perilous
>     EXCEPTION
>         WHEN OTHERS THEN -- nothing much
>     END;
>     IF SQLSTATE = '42000' THEN ...

I understand. My idea was detect local exception for local block, I can't
to see exception's information outside block and I cant get exception's
info from inner block. Your idea is easy for implementation, but oracle

http://www.unix.org.ua/orelly/oracle/prog2/ch13_03.htm

In Oracle doc:

If no exception has been raised, SQLCODE returns zero and SQLERRM returns
the message: ORA-0000: normal, successful completion.

If you reference SQLCODE outside of an exception section, it always
returns 0, which means normal, successful completion.

I tested it on Oracle 10g

 return integer  as
begin
  begin
    dbms_output.put_line('1: '||SQLCODE||' -> '||SQLERRM);
    raise_application_error(-20001, 'First exception');
  exception when others then
    dbms_output.put_line('2: '||SQLCODE||' -> '||SQLERRM);
    begin
      dbms_output.put_line('3: '||SQLCODE||' -> '||SQLERRM);
      raise_application_error(-20002, 'Second exception');
    exception when others then
      dbms_output.put_line('4: '||SQLCODE||' -> '||SQLERRM);
    end;
    dbms_output.put_line('5: '||SQLCODE||' -> '||SQLERRM);
  end;
  dbms_output.put_line('6: '||SQLCODE||' -> '||SQLERRM);
  return 1;
end;

select foo from dual

1: 0 -> ORA-0000: normal, successful completion
2: -20001 -> ORA-20001: First exception
3: -20001 -> ORA-20001: First exception
4: -20002 -> ORA-20002: Second exception
5: 0 -> ORA-0000: normal, successful completion
6: 0 -> ORA-0000: normal, successful completion

What it is mean?

So we can have only one procedure level scope variable, which is
initialized on start of exception and zeroized on the end of exception
block. This behavior is different from my patch, but is better for Oracle
compatibility and I prefere its.

I'll change patch, I can simplify it, if there will be agreement.

Best regards
Pavel Stehule