D'Arcy J.M. Cain wrote:
> On Thu, 20 Jan 2005 10:12:17 -0000
> "Mark Cave-Ayland" <m.cave-ayland@webbased.co.uk> wrote:
>
>>Thanks for the information. I seem to remember something similar to
>>this being discussed last year in a similar thread. My only real issue
>>I can see with this approach is that the trigger is fired for every
>>row, and it is likely that the database I am planning will have large
>>inserts of several hundred thousand records. Normally the impact of
>>these is minimised by inserting the entire set in one transaction. Is
>>there any way that your trigger can be modified to fire once per
>>transaction with the number of modified rows as a parameter?
>
>
> I don't believe that such a facility exists but before dismissing it you
> should test it out. I think that you will find that disk buffering (the
> system's as well as PostgreSQL's) will effectively handle this for you
> anyway.
Well, it looks like ROW_COUNT isn't set in a statement-level trigger
function (GET DIAGNOSTICS myvar=ROW_COUNT). Which is a shame, otherwise
it would be easy to handle. It should be possible to expose this
information though, since it gets reported at the command conclusion.
-- Richard Huxton Archonet Ltd
-- stmt_trig_test.sql --
BEGIN;
CREATE TABLE trigtest ( a int4 NOT NULL, b text, PRIMARY KEY (a)
);
CREATE FUNCTION tt_test_fn() RETURNS TRIGGER AS '
DECLARE nr integer; ro integer; nr2 integer;
BEGIN GET DIAGNOSTICS nr = ROW_COUNT; GET DIAGNOSTICS ro = RESULT_OID; SELECT count(*) INTO nr2 FROM
trigtest;
RAISE NOTICE ''nr = % / ro = % / nr2 = %'',nr,ro,nr2;
RETURN NULL;
END;
' LANGUAGE plpgsql;
CREATE TRIGGER tt_test AFTER INSERT OR UPDATE ON trigtest
FOR EACH STATEMENT
EXECUTE PROCEDURE tt_test_fn();
INSERT INTO trigtest VALUES (1,'a');
INSERT INTO trigtest VALUES (2,'b');
UPDATE trigtest SET b = 'x';
ROLLBACK;
