Re: Much Ado About COUNT(*) - Mailing list pgsql-hackers

Here's a possible solution... though I'm not sure about whether you find 
the pg_ prefix appropriate for this context.

-- Create a Test Relation
CREATE TABLE test_tbl (   test_id         BIGINT NOT NULL,   test_value      VARCHAR(128) NOT NULL,   PRIMARY KEY
(test_id));

-- Create COUNT Collector Relation
CREATE TABLE pg_user_table_counts (   schemaname       VARCHAR(64) NOT NULL,   tablename        VARCHAR(64) NOT NULL,
rowcount        BIGINT NOT NULL DEFAULT 0,   PRIMARY KEY (schemaname, tablename));
 

-- Populate Collector Relation
INSERT INTO pg_user_table_counts (schemaname, tablename)   (SELECT       schemaname,       tablename           FROM
 pg_tables    WHERE       schemaname != 'pg_catalog'       AND schemaname != 'information_schema'       AND tablename
!='pg_user_table_counts'   )
 
;

-- Create our Increment/Decrement Function
CREATE OR REPLACE FUNCTION pg_user_table_count_func () RETURNS TRIGGER 
AS $pg_user_table_count_func$   DECLARE       this_schemaname          VARCHAR(64);          BEGIN
       SELECT INTO this_schemaname           nspname       FROM           pg_namespace       WHERE           oid =
(SELECT                      relnamespace                   FROM                       pg_class                   WHERE
                     oid = TG_RELID);
 
       -- Decrement Count       IF (TG_OP = 'DELETE') THEN
           UPDATE pg_user_table_counts               SET rowcount = rowcount - 1               WHERE schemaname =
this_schemaname                  AND tablename = TG_RELNAME;
 
       ELSIF (TG_OP = 'INSERT') THEN
           UPDATE pg_user_table_counts               SET rowcount = rowcount + 1               WHERE schemaname =
this_schemaname                  AND tablename = TG_RELNAME;
 
       END IF;       RETURN NULL;   END;
$pg_user_table_count_func$ LANGUAGE plpgsql;

-- Create AFTER INSERT/UPDATE Trigger on our Test Table
CREATE TRIGGER test_tbl_aidt
AFTER INSERT OR DELETE ON test_tbl   FOR EACH ROW EXECUTE PROCEDURE pg_user_table_count_func();

-- INSERT to Test Relation
INSERT INTO test_tbl VALUES (1, 'Demo INSERT');

-- Query Collector
demodb=# SELECT * FROM pg_user_table_counts;schemaname |    tablename    | rowcount
------------+-----------------+----------public     | test_tbl        |        1
(1 row)

-- DELETE from Test Relation
DELETE FROM test_tbl;

-- Query Collector
emodb=# SELECT * FROM pg_user_table_counts;schemaname |    tablename    | rowcount
------------+-----------------+----------public     | test_tbl        |        0
(1 row)




Mark Kirkwood wrote:

> Jim C. Nasby wrote:
>
>> Does anyone have working code they could contribute? It would be best to
>> give at least an example in the docs. Even better would be something in
>> pgfoundry that helps build a summary table and the rules/triggers you
>> need to maintain it.
>
>
> http://developer.postgresql.org/docs/postgres/plpgsql-trigger.html#PLPGSQL-TRIGGER-SUMMARY-EXAMPLE 
>
>
> regards
>
> Mark
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if 
> your
>      joining column's datatypes do not match