From: Richard Huxton <dev@archonet.com>
To: Andrei Bintintan <klodoma@ar-sd.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Create trigger for auto update function
Date: Tue, 19 Jul 2005 10:11:57 +0100
Message-ID: <42DCC3DD.5000806@archonet.com> (raw)
In-Reply-To: <00e001c58b93$ad30b130$0b00a8c0@forge>
References: <00e001c58b93$ad30b130$0b00a8c0@forge>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, klodoma@ar-sd.net
Subject: Re: Create trigger for auto update function
In-Reply-To: <42DCC3DD.5000806@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox