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

From: Muiz <work(dot)muiz(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: plpgsql: how to get the exception's detail information?
Date: 2011-11-29 02:00:35
Message-ID: CAF2hCqwHuGtzni6uDMAwSSh8cBtBAj9xOVGPZJc1v4_RAh6cJg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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?

--
Regards,
*Muiz*

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message M. D. 2011-12-01 01:03:29 running totals with end of month line
Previous Message Belinda Cussen 2011-11-28 04:04:25 Re: Does anyone know of any issues around ARRAY UNNEST