--
-- Ultra Simple Count Workaround.
--
-- How To Use
--
-- 1. Add tables/functions to your database, ie, psql database_name < count.sql
-- 2. Add tables to count pool, eg, SELECT __count_add('my_table_name');
-- 3. Use it, ie, SELECT rows FROM __count_tables WHERE tablename = 'my_table_name';
--
-- Functions:
--   __count_add(VARCHAR)      - Add new table to managed count pool.
--   __count_delete(VARCHAR)   - Remove table to managed count pool.
--   __count_clean()           - Remove records for dropped/non-existent tables.
--   __count_handle_table()    - INSERT/DELETE Trigger.
--
--


--
-- Records stored here.
--
CREATE TABLE __count_tables (
    tablename VARCHAR(128),
    rows BIGINT,

    PRIMARY KEY(tablename)
);


--
-- Handle Trigger
--
CREATE OR REPLACE FUNCTION __count_handle_table ()
RETURNS TRIGGER AS $$
    BEGIN
        IF TG_OP = 'INSERT' THEN
            UPDATE __count_tables SET rows = (rows + 1) WHERE tablename = TG_RELNAME;
            RETURN NEW;
        END IF;

        IF TG_OP = 'DELETE' THEN
            UPDATE __count_tables SET rows = (rows - 1) WHERE tablename = TG_RELNAME;
            RETURN OLD;
        END IF;
    END;
$$ LANGUAGE plpgsql;


--
-- Add Table To Pool
--
CREATE OR REPLACE FUNCTION __count_add(VARCHAR)
RETURNS VARCHAR AS $$
    BEGIN
        EXECUTE 'INSERT INTO __count_tables SELECT ''' || $1 || ''', COUNT(*) FROM ' || $1;

        EXECUTE 'CREATE TRIGGER ' || $1 || '_count_handle AFTER INSERT OR DELETE ON ' || $1 || ' FOR EACH ROW EXECUTE PROCEDURE __count_handle_table()';

        RETURN $1 || ' Added To Pool';
    END;
$$ LANGUAGE plpgsql;


--
-- Delete Table To Pool
--
CREATE OR REPLACE FUNCTION __count_delete(VARCHAR)
RETURNS VARCHAR AS $$
    BEGIN
        EXECUTE 'DELETE FROM __count_tables WHERE tablename = ''' || $1 || '''';
        EXECUTE 'DROP TRIGGER ' || $1 || '_count_handle ON ' || $1;

        RETURN $1 || ' Deleted To Pool';
    END;
$$ LANGUAGE plpgsql;


--
-- Delete tables from __count_tables, that
-- have been dropped, or don't exist.
--
CREATE OR REPLACE FUNCTION __count_clean()
RETURNS VARCHAR AS $$
    DECLARE
        rows INTEGER;
    BEGIN
        DELETE FROM __count_tables WHERE tablename NOT IN
            (SELECT tablename FROM pg_tables);

        GET DIAGNOSTICS rows = ROW_COUNT;

        RETURN rows || ' Record(s) Deleted';
    END;
$$ LANGUAGE plpgsql;