Lists: | pgsql-hackers |
---|
From: | David Fetter <david(at)fetter(dot)org> |
---|---|
To: | PG Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | PL/PgSQL for counting all rows in all tables. |
Date: | 2004-09-29 00:00:49 |
Message-ID: | 20040929000049.GA29701@fetter.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
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(at)fetter(dot)org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778
Remember to vote!
From: | "Greg Sabino Mullane" <greg(at)turnstep(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: PL/PgSQL for counting all rows in all tables. |
Date: | 2004-09-29 01:38:15 |
Message-ID: | da437c9a6579f2426b3324b485c193ab@biglumber.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
-----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(at)turnstep(dot)com
PGP Key: 0x14964AC8 200409282138
-----BEGIN PGP SIGNATURE-----
iD8DBQFBWhIivJuQZxSWSsgRAm3cAJwLySzR5mpOHDT06LN6vj9M5zyyOQCgt4j1
gWDPcXJoUZbTlZvxxrE7fcY=
=MOMs
-----END PGP SIGNATURE-----