24

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.

1
  • I knew how to run a trigger ... I asked if there was a good way.
    – Joe
    Aug 16, 2011 at 23:09

1 Answer 1

21

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();
3
  • Doh ... I was thinking about it backwards (trying to make the trigger function callable by a procedure, not visa-versa).
    – Joe
    Jan 4, 2011 at 1:19
  • 1
    When I actually did it, I realized that 'row' was a reserved word, so I corrected the example so others won't spend as long debugging.
    – Joe
    Aug 16, 2011 at 23:12
  • 1
    I really should've followed up years ago. It took me qute a while before I managed to get everything working in my system ... and this method works, but the performance was god awful. (it went from 30 min to over a day to complete).
    – Joe
    Nov 4, 2014 at 19:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.