Re: Much Ado About COUNT(*)
От | Jonah H. Harris |
---|---|
Тема | Re: Much Ado About COUNT(*) |
Дата | |
Msg-id | 41F51409.5040907@tvi.edu обсуждение исходный текст |
Ответ на | Re: Much Ado About COUNT(*) (Mark Kirkwood <markir@coretech.co.nz>) |
Ответы |
Re: Much Ado About COUNT(*)
|
Список | 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
В списке pgsql-hackers по дате отправления: