Обсуждение: 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