Hi!
Not long time ago, I asked the list how to obtain the sizes of all the
databases on my postgres, to have a control on how growths the client's
applications.
Now I've found a solution and I thinked that it could be useful for
people working on ISP's offering postgres services or simply as an other
control utility then I'll put here for those who are interesed.
1-The function uses the database_size function that you can find in the
contrib directory (dbsize)
2-Create a table in one of your databases (with plpgsql active and the
addient permissions) with this structure
CREATE TABLE "mides" (
"nom" name NOT NULL,
"mida" int4 NOT NULL
);
3-This is the function you must add
CREATE FUNCTION mides_bds() RETURNS setof mides AS '
DECLARE
actual mides%ROWTYPE;
treball RECORD;
mida int4;
BEGIN
FOR treball IN SELECT datname FROM pg_database LOOP
SELECT INTO mida database_size(treball.datname);
mida:=mida/1024;
SELECT INTO actual a.datname,mida FROM pg_database a WHERE
a.datname=treball.datname;
RETURN NEXT actual;
END LOOP;
RETURN actual;
END;
'LANGUAGE 'plpgsql';
4-Now, if you execute SELECT * FROM mides_bds() you'll obtain the sizes
in Kb for all your databases
I hope it's useful for someone else, and once more, sorry for my english
--
********************************************************
Daniel Rubio Rodríguez
OASI (Organisme Autònom Per la Societat de la Informació)
c/ Assalt, 12
43003 - Tarragona
Tef.: 977.244.007 - Fax: 977.224.517
e-mail: drubio@oasi.org
********************************************************
