Re: Create trigger for auto update function

Andrei Bintintan wrote:
>
> Now, I want to write a trigger function that automatically updates the pass_md5 with the md5 function of the pass.
>
> I tried this:
>
> CREATE FUNCTION update_pass(integer) RETURNS integer AS $$
> UPDATE hoy SET pass_md5=md5(pass) WHERE id=$1;
> SELECT 1;
> $$ LANGUAGE SQL;
>
> and
>
> CREATE TRIGGER triger_users_pass_md5
> AFTER INSERT OR UPDATE
> ON hoy
> EXECUTE PROCEDURE update_pass(integer);

The simplest way to do this is with a BEFORE trigger, and just modifying
the NEW pseudo-record.

CREATE OR REPLACE FUNCTION maintain_pass_md5() RETURNS TRIGGER AS '
BEGIN
NEW.pass_md5 = md5(NEW.pass);
RETURN NEW;
END
' LANGUAGE plpgsql;

CREATE TRIGGER hoy_maintain_pass_md5
BEFORE INSERT OR UPDATE ON hoy
FOR EACH ROW EXECUTE PROCEDURE maintain_pass_md5();

Note that the function is defined to return type TRIGGER and that we
return NEW. If we returned NULL, the row would be skipped by the current
update statement. This means only one actual on-disk update takes place,
and as far as everyone is concerned pass_md5 automagically updates itself.

If the md5() function was actually an operation that would take a long
time, it might be worth checking whether pass has been changed:
IF NEW.pass IS DISTINCT FROM OLD.pass THEN
...
END IF
However, if you do this then you have to test TG_OP to see whether you
are inserting or updating - insert ops don't have OLD defined.

HTH
--
Richard Huxton
Archonet Ltd