Problems handling errors in PL/pgSQL

Folks,

I've run up against a problematic limitation of PL/pgSQL's
error-handling ability which could force me to re-write about 25 custom
functions. I'm hoping that you folks can show me a way around the
situation.

THE PROBLEM:

PL/pgSQL handles errors though "Implied Transactions", where the entire
function is a transaction and rolls back in the event that an error is
encountered. There is no way to declare a transaction within a PL/pgSQL
function, nor can one issue a ROLLBACK or COMMIT statement within a
function. As such, I have designed all of my data-modifiaction funcitons
to take advantage of this functionality, packaging all updates within a
single function.

However, not all types of errors are so trapped. The most problematic
un-trapped error is referential integrity: if an INSERT or UPDATE fails
because of a referential integrity violation, the PL/pgSQL function will
still see the statement as a success and not error out. Example:

Postgres ver. 7.1 RC2

CREATE TABLE "order_details" (
"order_detail_id" integer DEFAULT nextval('order_details_order_detail__seq'::text)
NOT NULL,
"order_usq" integer NOT NULL REFERENCES orders(usq),
"detail_id" integer NOT NULL,
"detail_req" boolean,
Constraint "order_details_pkey" Primary Key ("order_detail_id")
);

CREATE FUNCTION "fn_save_order_details" (integer,integer[],boolean[])
RETURNS integer AS '
DECLARE
v_order ALIAS for $1;
arr_details ALIAS for $2;
arr_req ALIAS for $3;
arr_loop INT2;
detail_no INT4;
detail_r BOOLEAN;
BEGIN
DELETE FROM order_details
WHERE order_usq = v_order;

arr_loop := 1;

WHILE arr_details[arr_loop] LOOP
detail_no := arr_details[arr_loop];
detail_r := COALESCE(arr_req[arr_loop], FALSE);
INSERT INTO order_details ( order_usq, detail_id, detail_req )
VALUES ( v_order, detail_no, detail_r );
arr_loop := arr_loop + 1;
END LOOP;

RETURN arr_loop - 1;
END;
' LANGUAGE 'plpgsql';

SELECT fn_save_order_details (7703, '{34,29,40}','{TRUE, TRUE, FALSE}');

---------
3

... thus supposedly reporting success: 3 order_details were saved.

However, it turns out that order 7703 has been deleted. Thus, the three
INSERTS we ran on order_details failed due to lack of referential
integrity; no records were saved. Yet the function did not error.

THE QUESTIONS:

1. Based on the above, it seems I have to go back and add data
validation and RAISE ERROR statements to all of my functions that do
INSERTS or UPDATES to tables with referential integrity triggers. Is
there a way around this?

2. Is there a plan to fix this kind of deficiency in Postgres
function/procedure error handling?

Thanks so much for your suggestions,

-Josh

______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh(at)agliodbs(dot)com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco