Re: Trapping errors from pl/perl (trigger) functions

On Sunday 01 July 2007 21:16, Michael Fuhr wrote:

> What have you tried and how did the outcome differ from your
> expectations?

The pl/perl trigger function in question generates an exception by
elog(ERROR, "message"). I also tried die(), which didn't make a difference.

When I do something on the table which the trigger function prevents, I get a
message saying ERROR: blablabla. When such an error is generated by a pl/pgsql
trigger function, I can trap the error with WHEN raise_exception. This does
not work for the exception generated by the pl/perl function.

> You could use "WHEN internal_error" or "WHEN others".  If that
> doesn't work then please post a simple but complete example that
> shows what you're trying to do.

Trapping "others" works, even though I think it's kind of klunky.

An example:

create table test_table
(
  field integer
);

create function test_function() returns trigger as $$
  elog(ERROR, "message");
  return;
$$ LANGUAGE plperl;

create trigger test_trigger before insert on test_table
  for each row execute_procedure test_function();

create function perform_actions() RETURNS VOID as $$
BEGIN
  BEGIN
    insert into test_table (field) values (1);
  EXCEPTION WHEN raise_exception THEN NULL; END;
END:
$$ language plpgsql;

select perform_actions();

The exception generated by the plperl function is not trapped by "WHEN
raise_exception", but it is by "WHEN others".

Is it a bug that postgres doesn't see pl/perl's error as an exception, or is
there a good reason for it?