PL/PgSQL for counting all rows in all tables.

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_countIN 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!