Thread: Re: fire trigger for a row without update?
On 2009-01-14, Gerhard Heift <ml-postgresql-20081012-3518@gheift.de> wrote: > Hello, > > is it possible to call a trigger for a row in a table without updating the > row? I want to do it in plpgsql. > Something like UPDATE table WHERE id = 10; when faced with that problem I do this: UPDATE table SET id=id WHERE id = 10;
On Thu, Jan 15, 2009 at 5:14 AM, Jasen Betts <jasen@xnet.co.nz> wrote: > On 2009-01-14, Gerhard Heift <ml-postgresql-20081012-3518@gheift.de> wrote: >> Hello, >> >> is it possible to call a trigger for a row in a table without updating the >> row? I want to do it in plpgsql. > >> Something like UPDATE table WHERE id = 10; > > when faced with that problem I do this: > > UPDATE table SET id=id WHERE id = 10; One small point. 'id' is probably indexed....to get better advantage of 'HOT', try updating a field that is not indexed instead. merlin
Merlin Moncure wrote: > On Thu, Jan 15, 2009 at 5:14 AM, Jasen Betts <jasen@xnet.co.nz> wrote: >> On 2009-01-14, Gerhard Heift <ml-postgresql-20081012-3518@gheift.de> wrote: >>> Hello, >>> >>> is it possible to call a trigger for a row in a table without updating the >>> row? I want to do it in plpgsql. >>> Something like UPDATE table WHERE id = 10; >> when faced with that problem I do this: >> >> UPDATE table SET id=id WHERE id = 10; > > One small point. 'id' is probably indexed....to get better advantage > of 'HOT', try updating a field that is not indexed instead. That's not necessary. HOT does a byte-wise comparison of the actual values involved, so as long as you don't really change the value, HOT will still kick in. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
On Thu, Jan 15, 2009 at 9:14 PM, Jasen Betts <jasen@xnet.co.nz> wrote: > On 2009-01-14, Gerhard Heift <ml-postgresql-20081012-3518@gheift.de> wrote: >> Hello, >> >> is it possible to call a trigger for a row in a table without updating the >> row? I want to do it in plpgsql. > >> Something like UPDATE table WHERE id = 10; > > when faced with that problem I do this: > > UPDATE table SET id=id WHERE id = 10; An alternative would be to set up a separate function that does the work, taking a record as argument, and just have the trigger call that. CREATE OR REPLACE FUNCTION do_stuff(table) RETURNS void AS $$ -- Do stuff ... $$ LANGUAGE plpgsql VOLATILE; CREATE OR REPLACE FUNCTION table_update_trigger() RETURNS trigger AS $$ BEGIN PERFORM do_stuff(NEW); RETURN NULL; END; $$ LANGUAGE plpgsql VOLATILE; CREATE TRIGGER do_stuff AFTER UPDATE ON table FOR EACH ROW EXECUTE PROCEDURE table_update_trigger(); Now your trigger will call do_stuff() with the relevant record whenever you really update the table, and if you want to do stuff without updating, you can just call do_stuff() directly as well, for example: SELECT do_stuff(table) FROM table WHERE needs_stuff_done; Cheers, BJ