Dear all,
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?