From: | "Jonah H(dot) Harris" <jharris(at)tvi(dot)edu> |
---|---|
To: | Mark Kirkwood <markir(at)coretech(dot)co(dot)nz> |
Cc: | "Jim C(dot) Nasby" <decibel(at)decibel(dot)org>, Ron Mayer <rm_pg(at)cheapcomplexdevices(dot)com>, Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: Much Ado About COUNT(*) |
Date: | 2005-01-24 15:28:09 |
Message-ID: | 41F51409.5040907@tvi.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-announce pgsql-hackers pgsql-patches |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Joshua D. Drake | 2005-01-24 23:51:31 | Mammoth PostgreSQL Replicator 1.4 Released (Updated) |
Previous Message | Vita Voom Software | 2005-01-24 11:46:43 | Released *beta* v3.00 of the pgExpress driver |
From | Date | Subject | |
---|---|---|---|
Next Message | Jonah H. Harris | 2005-01-24 15:35:40 | Re: Concurrent free-lock |
Previous Message | Greg Stark | 2005-01-24 14:57:02 | Re: Locale agnostic unicode text |
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Eisentraut | 2005-01-24 16:24:59 | Re: [PATCHES] Merge pg_shadow && pg_group -- UNTESTED |
Previous Message | Neil Conway | 2005-01-24 06:21:41 | LRU |