Re: Much Ado About COUNT(*)

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