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
>
>
