Transactions and Exceptions

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
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.