Re: Create trigger for auto update function

Off topic
:) I think we posted in the same time :))

----- Original Message ----- 
From: "Richard Huxton" <dev@archonet.com>
To: "Andrei Bintintan" <klodoma@ar-sd.net>
Cc: <pgsql-sql@postgresql.org>
Sent: Tuesday, July 19, 2005 12:11 PM
Subject: Re: [SQL] 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
>