Message-ID: <42DCC3DD.5000806@archonet.com>
Date: Tue, 19 Jul 2005 10:11:57 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla Thunderbird 1.0.2 (X11/20050317)
MIME-Version: 1.0
To: Andrei Bintintan <klodoma@ar-sd.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Create trigger for auto update function
References: <00e001c58b93$ad30b130$0b00a8c0@forge>
In-Reply-To: <00e001c58b93$ad30b130$0b00a8c0@forge>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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