Folks,

I've noticed that when coming into an organization, I need to do some
counting on what's in all the tables in a db.  This shortens that
process considerably, with the usual caveat that count(*) is a heavy
operation.

By the way, the 3 lines following "godawful hack" point to something
PL/PgSQL ought (imho) to be able to do, namely something like

EXECUTE INTO [ record | rowtype | type ] [sql TEXT string returning a single row];

Here 'tis: version 0.01...

CREATE TYPE table_count AS (table_name TEXT, num_rows INTEGER);

CREATE OR REPLACE FUNCTION count_em_all () RETURNS SETOF table_count
AS '
DECLARE
    the_count RECORD;
    t_name RECORD;
    r table_count%ROWTYPE;
BEGIN
    FOR t_name IN
        SELECT c.relname
        FROM
            pg_catalog.pg_class c
                LEFT JOIN
            pg_namespace n
                ON
            n.oid = c.relnamespace
        WHERE
            c.relkind = ''r''
                AND
            n.nspname = ''public''
        ORDER BY 1
    LOOP
        -- The next 3 lines are a godawful hack. :P
        FOR the_count IN EXECUTE ''SELECT COUNT(*) AS "count" FROM '' || t_name.relname
        LOOP
        END LOOP; 
        r.table_name := t_name.relname;
        r.num_rows := the_count.count;
        RETURN NEXT r;
    END LOOP;
    RETURN;
END;
' LANGUAGE plpgsql;

COMMENT ON FUNCTION count_em_all () IS 'Spits out all tables in the public schema and the exact row counts for each.';

-- 
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!

Tue, 28 Sep 2004 17:00:49 -0700
David Fetter <david@fetter.org>
PL/PgSQL for counting all rows in all tables.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
 
 
My version:
 
ANALYZE;
 
SELECT n.nspname, relname, reltuples
FROM pg_class c, pg_namespace n
WHERE c.relnamespace=n.oid
AND relkind='r'
AND NOT n.nspname ~ '^pg_'
ORDER BY 1,2;
 
 
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200409282138
 
-----BEGIN PGP SIGNATURE-----
 
iD8DBQFBWhIivJuQZxSWSsgRAm3cAJwLySzR5mpOHDT06LN6vj9M5zyyOQCgt4j1
gWDPcXJoUZbTlZvxxrE7fcY=
=MOMs
-----END PGP SIGNATURE-----



Wed, 29 Sep 2004 01:38:15 -0000
"Greg Sabino Mullane" <greg@turnstep.com>
Re: PL/PgSQL for counting all rows in all tables.
"Greg Sabino Mullane" wrote:
> ANALYZE;
>  
> SELECT n.nspname, relname, reltuples
> FROM pg_class c, pg_namespace n
> WHERE c.relnamespace=n.oid
> AND relkind='r'
> AND NOT n.nspname ~ '^pg_'
> ORDER BY 1,2;

Maybe this gem should be passed onto the pgadmin folks.  When you
click on a table name in the interface it does what I can only
presume is a count(*) from relation, which takes forever on enormous
relations.  It then claims this to be a row estimate anyway, so
they could probably drop the analyze.

Ian

--
Ian Freislich

Tue, 05 Oct 2004 10:57:26 +0200
Ian FREISLICH <if@hetzner.co.za>
Re: PL/PgSQL for counting all rows in all tables. 