Thread: Trigger function volatility

I have some data that I would like to store in lowercase, and so I've
created a function and a trigger:


create or replace function gl_lower() returns trigger as $$
begin
     new.sender := lower(new.sender);
     new.recipient := lower(new.recipient);
     return new;
end;
$$ language plpgsql;
create trigger gl_lower
     before insert or update on gl_exemptions
     for each row execute procedure gl_lower();


I'm wondering, though, if I can declare it IMMUTABLE. The function itself
doesn't query the database or modify the database directly. Potentially
the data which is about to be inserted/updated gets modified, but my
understanding is that the new data isn't committed until all of the before
triggers are processed.

Can anyone think of any issues with IMMUTABLE in this case? Will there be
any noticeable benefit? My initial tests thus far have all passed.

Kindest regards,

--
Tyler

Tyler Mitchell <fission@ldx.ca> writes:
> Can anyone think of any issues with IMMUTABLE in this case?

It's incorrect.  The function is altering NEW, so I hardly see how you
can think it's immutable --- it has side effects.

The system might let you do this at the moment, because it pays
hardly any attention to the volatility classification of a trigger
function anyhow; but that doesn't make it a good idea.

            regards, tom lane