Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

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

1 Answer 1

up vote 18 down vote accepted

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();
share|improve this answer
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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