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