fire trigger for a row without update?

Gerhard Heift wrote:

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;
or PERFORM table.trigger('UPDATE', row) WHERE id = 10;

Think twice if you really need that - it sounds a little odd.

But you could do:

UPDATE tab SET id = 10 WHERE id = 10;

or something similar. This would of course create a new row version, but
it would do what you want.

Yours,
Laurenz Albe

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