Re: plpgsql: how to get the exception's detail information?

2011/11/29 Muiz <work(dot)muiz(at)gmail(dot)com>:
>    I write a function to execute a sql string. E.g. "update tableA set
> field1='abc' where name='123'; deletee from tableB where id=333;"
>    The following is my function:
> -----------------------------------------------------------------------------------------------------------------------------
> CREATE OR REPLACE FUNCTION no_err_rollback()
>   RETURNS boolean AS
> $BODY$
> BEGIN
>     ROLLBACK;
>     RETURN TRUE;
> EXCEPTION
>      WHEN others THEN
>             RETURN TRUE;
> END
> $BODY$
>   LANGUAGE plpgsql;
>

I think this does not do what you think.

Transaction control commands (like ROLLBACK) inside functions does not
work in PostgreSQL.
Using ROLBACK in PgSQL will raise an exception (which you forcibly
ignored above).
This is a big feature which is sometimes called "autonomous
transactions" and is not yet implemented, AFAIK.
You can test this quite easilly; use txid_current() function to check
current transaction ID.

>  1. when I execute a sql, can I get the total records user updated or
> deleted ?
see GET DIAGNOSTICS ->
http://www.postgresql.org/docs/9.0/static/plpgsql-statements.html
NOTE: this will count rows affected by last query only.

>  2. if I cache the exceptions, can I get the detail information?
what do you mean by "cache exceptions"?