Thread: Create trigger for auto update function
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;
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);
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.
> CREATE TRIGGER triger_users_pass_md5 > AFTER INSERT OR UPDATE > ON hoy > EXECUTE PROCEDURE update_pass(integer); Try : FOR EACH ROW EXECUTE
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;$$ LANGUAGESQL; CREATE TRIGGER triger_users_pass_md5 AFTER INSERT OR UPDATE ON hoy FOR EACH ROW EXECUTE PROCEDURE update_pass; DAQ
> 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 > >
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;$$ LANGUAGESQL; DAQ
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
CREATE OR REPLACE FUNCTION u9() RETURNS TRIGGER AS' BEGIN NEW.pass_md5=md5(NEW.pass); return NEW; END 'language plpgsql; CREATE TRIGGER t8BEFORE INSERT OR UPDATEON hoy FOR EACH ROW EXECUTE PROCEDURE u9(); Ok. This is the solution. It works well, for inserts and updates. Took some time to figure it out. (ignore the function names --- test functions) Best regards, Andy. ----- 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 > >
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 > >