From: Andy <frum@ar-sd.net>
To: Richard Huxton <dev@archonet.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Create trigger for auto update function
Date: Tue, 19 Jul 2005 12:27:54 +0300
Message-ID: <01d301c58c44$25cddb80$0b00a8c0@forge> (raw)
References: <00e001c58b93$ad30b130$0b00a8c0@forge>
<42DCC3DD.5000806@archonet.com>
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
>
>
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: frum@ar-sd.net, dev@archonet.com
Subject: Re: Create trigger for auto update function
In-Reply-To: <01d301c58c44$25cddb80$0b00a8c0@forge>
* 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