This text is taken from PostgreSQL General Bits 27-Oct-2003 Issue: 49 column at
http://www.varlena.com/varlena/GeneralBits/49.php

-------------------------------------------------------------------------------


   Tracking the row count
   [GENERAL] create triggers 20-Oct-2003

   A trigger to update the row count is needed. The actual row count is
   important for the application and count(*) by nature is too slow.

   The way to implement a proper row counter is to create a trigger on
   the table which needs to be counted. This trigger will increment or
   decrement the count on insert or delete.

   There are a couple of interesting issues with the row counting
   implementation. The first is that the counter trigger function can
   work for both insert and delete and with any table by using trigger
   information in plpgsql. The second is the issue of initializing the
   rowcount table.

   Suppose you had the table mystuff and set up the table rowcount to
   hold the row count of many tables.
        CREATE TABLE mystuff (
                name  text NOT NULL,
                description text,
                PRIMARY KEY (name));

        CREATE TABLE rowcount (
                table_name  text NOT NULL,
                total_rows  bigint,
                PRIMARY KEY (table_name));

   Most people are familiar with the use of OLD and NEW variables in
   trigger functions, but there are several other informational variables
   available.

             Name                      Meaning
          TG_NAME    Name of the trigger
          TG_WHEN    BEFORE or AFTER
          TG_LEVEL   ROW or STATEMENT
          TG_OP      INSERT, UPDATE or DELETE
          TG_RELID   Relation OID of table with trigger
          TG_RELNAME Table name of table with trigger
          TG_NARGS   Number of arguments in row being updated
          TG_ARGV[]  Text array of datatypes in row being updated

    Note that Statement level triggers are available in 7.4 and forward.

   The count_rows() trigger function uses TG_OP and TG_RELNAME. TG_OP
   indicates whether it is a DELETE or INSERT and TG_RELNAME is used to
   store the count in the countrows table by table name. This is the
   function:
        CREATE OR REPLACE FUNCTION count_rows()
        RETURNS TRIGGER AS
        '
           BEGIN
              IF TG_OP = ''INSERT'' THEN
                 UPDATE rowcount
                    SET total_rows = total_rows + 1
                    WHERE table_name = TG_RELNAME;
              ELSIF TG_OP = ''DELETE'' THEN
                 UPDATE rowcount
                    SET total_rows = total_rows - 1
                    WHERE table_name = TG_RELNAME;
              END IF;
              RETURN NULL;
           END;
        ' LANGUAGE plpgsql;

   There are many ways the various TG variables can be used to write
   generic triggers. Different courses of action can be taken based on
   the exact definition of the trigger. The data in the row can be
   accessed via NEW and OLD and information about their datatypes is
   available if decisions or actions need to be done based on the data
   type.

   Now we will look at what is necessary to initialize the trigger. If
   the trigger function and the tables are all defined in the same
   transaction, then initialization is not necessary. However, most of
   the time if row counting functionality is being added to an existing
   table, the base row count must be ascertained.

   The initialization must be done in a single transaction. The target
   table is locked to prevent updates during this initialization. Then
   the trigger is created and the rowcount is updated with the current
   row count. Once the initialization transaction is committed, then your
   counter is operational. Don't forget to test it!
        BEGIN;
           -- Make sure no rows can be added to mystuff until we have finished
           LOCK TABLE mystuff IN SHARE ROW EXCLUSIVE MODE;

           create TRIGGER countrows
              AFTER INSERT OR DELETE on mystuff
              FOR EACH ROW EXECUTE PROCEDURE count_rows();

           -- Initialise the row count record
           DELETE FROM rowcount WHERE table_name = 'mystuff';

           INSERT INTO rowcount (table_name, total_rows)
           VALUES  ('mystuff',  (SELECT COUNT(*) FROM mystuff));

        COMMIT;

        --
        -- Testing
        --
        insert into mystuff values ('abacus','mathmatics');
        insert into mystuff values ('bee','insect');
        select * from rowcount;
        insert into mystuff values ('dog','pet');
        insert into mystuff values ('cathedral','building');
        select * from rowcount;
        select * from mystuff;
        delete from mystuff where name='abacus';
        select * from rowcount;
        select * from mystuff;

   Contributors: Ling Xiaoyu cdu_lx at yahoo.com Oliver Elphick olly at
   lfix.co.uk elein at varlena.com