Обсуждение: plpgsql: how to get the exception's detail information?
Dear all,
--
Regards,
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;
CREATE OR REPLACE FUNCTION execsqls(sqls character varying)
RETURNS boolean AS
$BODY$
DECLARE
r BOOLEAN;
BEGIN
EXECUTE sqls;
-- TODO-1: I want to know how many records the input sqls effects?
RETURN TRUE;
EXCEPTION
WHEN others THEN
SELECT no_err_rollback() INTO r;
-- TODO-2: I want to get the exception's code and detail information. can I ?
RAISE EXCEPTION 'Error: %', 'abc';
END;
$BODY$
LANGUAGE plpgsql;
-----------------------------------------------------------------------------------------------------------------------------
My Questions are:
1. when I execute a sql, can I get the total records user updated or deleted ?
2. if I cache the exceptions, can I get the detail information?
Regards,
Muiz
2011/11/29 Muiz <work.muiz@gmail.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"?