Is there a good way to run a trigger for each record in a postgres table?

Question

I have a system where I can't control the design of some tables (replicated via Slony-I), and so I have a series of what we refer to as 'shadow tables', where I extract some information out of the replicated tables, and store it in the processed form that I need, while stripping out the records that I want to ignore.

Right now, after setting up a new replica, I run an update and set a value back to itself (eg, UPDATE tablename SET field=field) to force the trigger to run, but some of the tables are millions of records, and growing, and it can take 30min. (and then there's the vaccuum, too).

Is there some better way to trigger it, or some way to write a function such that it'll work with either input passed in or NEW depending on calling context? I'm reluctant to keep two different functions around, as I've seen too many times where one gets updated, and not the other.

Answer 1

It can be done, using the following template:

CREATE TABLE tablename ( ... );

/* for direct invocation */
CREATE FUNCTION propagate_data(newrow tablename) RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO other_table VALUES (newrow.a, newrow.b, ...);
END:
$$;

/* trigger function wrapper */
CREATE FUNCTION propagate_data_trg() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    PERFORM propagate_data(NEW);
END;
$$;

CREATE TRIGGER propagate_data AFTER INSERT ON tablename FOR EACH ROW
    EXECUTE PROCEDURE propagate_data_trg();