On Thu, 20 Jan 2005 10:12:17 -0000 "Mark Cave-Ayland" <[EMAIL PROTECTED]> 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;
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
