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.

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

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 $$
    INSERT INTO other_table VALUES (newrow.a, newrow.b, ...);

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

    EXECUTE PROCEDURE propagate_data_trg();
  • Doh ... I was thinking about it backwards (trying to make the trigger function callable by a procedure, not visa-versa). – Joe Jan 4 '11 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 '11 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 '14 at 19:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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