Dear all,
I'm having a problem with transactions and exceptions and need your advice.
I want a function to do two things:
- log something to a table (which is basically an insert)
- raise an exception under certain conditions
My problem is that when I raise the exception the insert is rolled back.
How can I work around that?
Example (pseudocode)
CREATE OR REPLACE FUNCTION public.test() RETURNS void AS
$body$
DECLARE
num integer;
BEGIN
--log start of function
insert into logtable(fieldX, fieldY, fieldZ) values ('test', null, 'start');
--process some 2,500,000 records
--srffuntion is a plperlu function that fetches records from eg Oracle using DBI
for rec in select * from srffunction loop
begin
insert into targettable(field1, ..., fieldN) values (rec.field1, ..., rec.fieldN);
exception
when others
--log why this record could not be inserted
insert into logtable(fieldX, fieldY, fieldZ) values ('test', rec.id, SQLERRM);
num += 1;
end;
end loop;
--if some records were skipped the calling application should know
--by the way, this function gets called through ADO like
--conn.execute('select test()',,adCmdText)
if num then
raise exception '% records skipped', num;
end if;
--log end of function
insert into logtable(fieldX, fieldY, fieldZ) values ('test', null, 'end');
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
So I want to use 'raise exception' as the way to inform the calling application that something went wrong
but the inserts that have been done are ok and thus must be committed instead of rolled back by the
'raise exception'. How can I do that?
Thanks for any advice or ideas.