Re: fire trigger for a row without update?

On Thu, Jan 15, 2009 at 9:14 PM, Jasen Betts <jasen(at)xnet(dot)co(dot)nz> wrote:
> On 2009-01-14, Gerhard Heift <ml-postgresql-20081012-3518(at)gheift(dot)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