-- 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
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
