Trigger to check valid input

Question

I am inserting a lot of measurement data from different sources in a postgres database. The data are a measured value and an uncertainty (and a lot of auxilliary data) The problem is that in some cases I get an absolute error value eg 123 +/- 33, in other cases, I get a relative error as a percentage of the measured value, eg 123 +/- 10%. I would like to store all the measurements with absolute error, i.e the latter should be stored as 123 +/- 12.3 - (at this point, I don't care too much about the valid number of digits)

My idea is to use a trigger to do this. Basically, if the error is numeric, store it as is, if it is non-numeric, check if the last character is '%', in that case, multiply it with the measured value, divide by 100 and store the resulting value. I got an isnumeric-function from here: isnumeric() with PostgreSQL which works fine. But when I try to make this into a trigger, it seems as if the input is checked for validity even before the trigger fires, so that the insert is abortet before I get any possibility to do anything with the values.

my triggerfunction: (need to do the calculation, just setting the error to 0 here...

create function my_trigger_function()
returns trigger as'
begin
  if not isnumeric(new.err) THEN
     new.err=0;
  end if;
  return new;
end' language 'plpgsql';

then I connect it to the table:

create trigger test_trigger
 before insert on test
 for each row
 execute procedure my_trigger_function();

Doing this, I would expect to get val=123 and err=0 for the following insert

 insert into test(val,err) values(123,'10%');

but the insert fails with "invalid input syntax for type numeric" which then must be triggered before my trigger gets any possibility to see the data (or I have misunderstood something basic). Is it possible to make the new.err data-type agnostic or can I run the trigger even earlier or is what I want to do just plain impossible?

It's not possible with a trigger because the SQL parser fails before. The closest alternative is to provide a function `myfunc(text) returns numeric` and apply it in the VALUES clause. — Daniel Vérité, Mar 04 '14 at 15:52
Thanks, that was what I feared - back to the drawing board... :-/ — MortenSickel, Mar 05 '14 at 08:55
@DanielVérité, if you rewrite your commend as an answer, I can accept it. — MortenSickel, Mar 05 '14 at 10:31

score 1 · Answer 1 · answered Mar 05 '14 at 14:27

It's not possible with a trigger because the SQL parser fails before.

When the trigger is launched, the NEW.* columns already have their definitive types matching the destination columns.

The closest alternative is to provide a function converting from text to numeric implementing your custom syntax rules and apply it in the VALUES clause:

insert into test(val,err) values(123, custom_convert('10%'));

score 0 · Accepted Answer · answered Mar 05 '14 at 15:47

Daniel answered on my original question - and I found out I had to think otherwise. His proposal for how to do it may work for others, but the way my system interfaces to the database by fetching table and column names directly from the database, it would not work well.

Instead I added a boolean field relerr to the measurement table

alter table measure add relerr boolean default false;

Then I made a trigger that checks if relerr is true - indicating that I am trying to store a relative error, if so, it recalculates the error column (called prec for precision)

 CREATE FUNCTION calc_fromrel_error()
 RETURNS trigger as'
    IF NEW.relerr THEN
        NEW.prec=NEW.prec*NEW.value/100;
        NEW.relerr=FALSE;
    END IF;
    return NEW;
 END' language 'plpgsql';

and then

 create trigger meas_calc_relerr_trigger
 before update on measure
    for each row
    execute procedure calc_fromrel_error();

voila, by doing a

INSERT into measure (value,prec,relerr) values(220,10,true);

I get the table populated with 220,22,false. Inserted values should normally never be updated, if that for some strange reason should happen, I will be able to calculate the prec column manually.

Trigger to check valid input

2 Answers2