pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCreate trigger for auto update function
7+ messages / 5 participants
[nested] [flat]
* Create trigger for auto update function
@ 2005-07-18 12:24 Andrei Bintintan <klodoma@ar-sd.net>
2005-07-18 13:21 ` Re: Create trigger for auto update function PFC <lists@boutiquenumerique.com>
2005-07-18 13:32 ` Re: Create trigger for auto update function daq <daq@ugyvitelszolgaltato.hu>
2005-07-19 09:11 ` Re: Create trigger for auto update function Richard Huxton <dev@archonet.com>
0 siblings, 3 replies; 7+ messages in thread
From: Andrei Bintintan @ 2005-07-18 12:24 UTC (permalink / raw)
To: pgsql-sql
Hi to all,
I have a table:
create table hoy(
id serial,
pass varchar(40),
pass_md5 varchar(40);
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);
But it works not.
When I create the trigger it says that function does not exist.
I also tried with:
CREATE OR REPLACE FUNCTION user2(integer)RETURNS TRIGGER AS'
BEGIN
UPDATE users SET pass_md5=md5(pass) WHERE id=$1;
return NULL;
END
'language plpgsql;
.... the same
Need some help!!!!
Andy.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Create trigger for auto update function
2005-07-18 12:24 Create trigger for auto update function Andrei Bintintan <klodoma@ar-sd.net>
@ 2005-07-18 13:21 ` PFC <lists@boutiquenumerique.com>
2 siblings, 0 replies; 7+ messages in thread
From: PFC @ 2005-07-18 13:21 UTC (permalink / raw)
To: Andrei Bintintan <klodoma@ar-sd.net>; pgsql-sql
> CREATE TRIGGER triger_users_pass_md5
> AFTER INSERT OR UPDATE
> ON hoy
> EXECUTE PROCEDURE update_pass(integer);
Try : FOR EACH ROW EXECUTE
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Create trigger for auto update function
2005-07-18 12:24 Create trigger for auto update function Andrei Bintintan <klodoma@ar-sd.net>
@ 2005-07-18 13:32 ` daq <daq@ugyvitelszolgaltato.hu>
2005-07-19 07:55 ` Re: Create trigger for auto update function Andy <frum@ar-sd.net>
2 siblings, 1 reply; 7+ messages in thread
From: daq @ 2005-07-18 13:32 UTC (permalink / raw)
To: Andrei Bintintan <klodoma@ar-sd.net>; +Cc: pgsql-sql
Hello Andrei,
Monday, July 18, 2005, 2:24:41 PM, you wrote:
AB> Hi to all,
AB> I have a table:
AB> create table hoy(
AB> id serial,
AB> pass varchar(40),
AB> pass_md5 varchar(40);
AB> Now, I want to write a trigger function that automatically updates the pass_md5 with the md5 function of the pass.
AB> I tried this:
AB> CREATE FUNCTION update_pass(integer) RETURNS integer AS $$
AB> UPDATE hoy SET pass_md5=md5(pass) WHERE id=$1;
AB> SELECT 1;
AB> $$ LANGUAGE SQL;
AB> and
AB> CREATE TRIGGER triger_users_pass_md5
AB> AFTER INSERT OR UPDATE
AB> ON hoy
AB> EXECUTE PROCEDURE update_pass(integer);
What will be the param of the trigger procedure?
Try this way:
CREATE FUNCTION update_pass() RETURNS integer AS $$
UPDATE hoy SET pass_md5=md5(pass) WHERE id=new.id;
SELECT 1;
$$ LANGUAGE SQL;
CREATE TRIGGER triger_users_pass_md5
AFTER INSERT OR UPDATE
ON hoy FOR EACH ROW
EXECUTE PROCEDURE update_pass;
DAQ
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Create trigger for auto update function
2005-07-18 12:24 Create trigger for auto update function Andrei Bintintan <klodoma@ar-sd.net>
2005-07-18 13:32 ` Re: Create trigger for auto update function daq <daq@ugyvitelszolgaltato.hu>
@ 2005-07-19 07:55 ` Andy <frum@ar-sd.net>
2005-07-19 08:10 ` Re: Create trigger for auto update function daq <daq@ugyvitelszolgaltato.hu>
0 siblings, 1 reply; 7+ messages in thread
From: Andy @ 2005-07-19 07:55 UTC (permalink / raw)
To: daq16 <daq@ugyvitelszolgaltato.hu>; +Cc: pgsql-sql
> CREATE FUNCTION update_pass() RETURNS integer AS $$
> UPDATE hoy SET pass_md5=md5(pass) WHERE id=new.id;
> SELECT 1;
> $$ LANGUAGE SQL;
>
> CREATE TRIGGER triger_users_pass_md5
> AFTER INSERT OR UPDATE
> ON hoy FOR EACH ROW
> EXECUTE PROCEDURE update_pass;
I understand the ideea, but don't know how to apply it.
I also receive the error that NEW must be definde as a rule.
Still... not working...
----- Original Message -----
From: "daq" <daq@ugyvitelszolgaltato.hu>
To: "Andrei Bintintan" <klodoma@ar-sd.net>
Cc: <Pgsql-sql@postgresql.org>
Sent: Monday, July 18, 2005 4:32 PM
Subject: Re: [SQL] Create trigger for auto update function
> Hello Andrei,
>
> Monday, July 18, 2005, 2:24:41 PM, you wrote:
>
> AB> Hi to all,
>
> AB> I have a table:
> AB> create table hoy(
> AB> id serial,
> AB> pass varchar(40),
> AB> pass_md5 varchar(40);
>
> AB> Now, I want to write a trigger function that automatically updates the
> pass_md5 with the md5 function of the pass.
>
> AB> I tried this:
>
> AB> CREATE FUNCTION update_pass(integer) RETURNS integer AS $$
> AB> UPDATE hoy SET pass_md5=md5(pass) WHERE id=$1;
> AB> SELECT 1;
> AB> $$ LANGUAGE SQL;
>
> AB> and
>
> AB> CREATE TRIGGER triger_users_pass_md5
> AB> AFTER INSERT OR UPDATE
> AB> ON hoy
> AB> EXECUTE PROCEDURE update_pass(integer);
>
> What will be the param of the trigger procedure?
>
> Try this way:
>
> CREATE FUNCTION update_pass() RETURNS integer AS $$
> UPDATE hoy SET pass_md5=md5(pass) WHERE id=new.id;
> SELECT 1;
> $$ LANGUAGE SQL;
>
> CREATE TRIGGER triger_users_pass_md5
> AFTER INSERT OR UPDATE
> ON hoy FOR EACH ROW
> EXECUTE PROCEDURE update_pass;
>
>
> DAQ
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Create trigger for auto update function
2005-07-18 12:24 Create trigger for auto update function Andrei Bintintan <klodoma@ar-sd.net>
2005-07-18 13:32 ` Re: Create trigger for auto update function daq <daq@ugyvitelszolgaltato.hu>
2005-07-19 07:55 ` Re: Create trigger for auto update function Andy <frum@ar-sd.net>
@ 2005-07-19 08:10 ` daq <daq@ugyvitelszolgaltato.hu>
0 siblings, 0 replies; 7+ messages in thread
From: daq @ 2005-07-19 08:10 UTC (permalink / raw)
To: Andy <frum@ar-sd.net>; +Cc: pgsql-sql
Hello Andy,
Tuesday, July 19, 2005, 9:55:41 AM, you wrote:
>> CREATE FUNCTION update_pass() RETURNS integer AS $$
>> UPDATE hoy SET pass_md5=md5(pass) WHERE id=new.id;
>> SELECT 1;
>> $$ LANGUAGE SQL;
>>
>> CREATE TRIGGER triger_users_pass_md5
>> AFTER INSERT OR UPDATE
>> ON hoy FOR EACH ROW
>> EXECUTE PROCEDURE update_pass;
A> I understand the ideea, but don't know how to apply it.
A> I also receive the error that NEW must be definde as a rule.
A> Still... not working...
Sorry! My fault. Trigger porcedure returns OPAQUE type.
CREATE FUNCTION update_pass() RETURNS OPAQUE AS $$
UPDATE hoy SET pass_md5=md5(pass) WHERE id=new.id;
SELECT 1;
$$ LANGUAGE SQL;
DAQ
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Create trigger for auto update function
2005-07-18 12:24 Create trigger for auto update function Andrei Bintintan <klodoma@ar-sd.net>
@ 2005-07-19 09:11 ` Richard Huxton <dev@archonet.com>
2005-07-19 09:27 ` Re: Create trigger for auto update function Andy <frum@ar-sd.net>
2 siblings, 1 reply; 7+ messages in thread
From: Richard Huxton @ 2005-07-19 09:11 UTC (permalink / raw)
To: Andrei Bintintan <klodoma@ar-sd.net>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Create trigger for auto update function
2005-07-18 12:24 Create trigger for auto update function Andrei Bintintan <klodoma@ar-sd.net>
2005-07-19 09:11 ` Re: Create trigger for auto update function Richard Huxton <dev@archonet.com>
@ 2005-07-19 09:27 ` Andy <frum@ar-sd.net>
0 siblings, 0 replies; 7+ messages in thread
From: Andy @ 2005-07-19 09:27 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql
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
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2005-07-19 09:27 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-07-18 12:24 Create trigger for auto update function Andrei Bintintan <klodoma@ar-sd.net>
2005-07-18 13:21 ` PFC <lists@boutiquenumerique.com>
2005-07-18 13:32 ` daq <daq@ugyvitelszolgaltato.hu>
2005-07-19 07:55 ` Andy <frum@ar-sd.net>
2005-07-19 08:10 ` daq <daq@ugyvitelszolgaltato.hu>
2005-07-19 09:11 ` Richard Huxton <dev@archonet.com>
2005-07-19 09:27 ` Andy <frum@ar-sd.net>
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