Re: Much Ado About COUNT(*)

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;