Hi,
We have several users working on a 8.4 database, using it as a
back-end for several related apps and transfering data to and from it.
The database tends to get a bit messy, so i've made a little table to
provide an overview.
This table is truncated and refilled daily, it shows all tables and
views in the database and :
* the owner
* number of records (estimation)
* it's size on disk
* the description
There's a view on the table that shows the size as pg_size_pretty
When you edit the description in the table (or the view, but no
support in pgAdmin), the comment in the system tables is updated also.
I attatched my code, hope some people find it handy, sorry for the
names and comments being in dutch.
Now, i would like to improve this thing and add a creation date for the table.
I have some questions about that.
1. I think that there is no such information in the system tables. is
that correct?
I am planning to change the mechanism, so that the table is not
truncated, but new tables are inserted in the overview and dropped
tables are deleted from it. I need to do that in 2 steps (delete and
insert).
Then i can add a creation-date column which i will fill with 'today'.
2. i would like to go back in time. I think that i will just look up
the creation date for the files in the data directory and translate
their oid's to the object names and then update their dates. This
would of course only work from the last restore. Is that a good way to
do it?
Thanks,
WBL
--
"Patriotism is the conviction that your country is superior to all
others because you were born in it." -- George Bernard Shaw
CREATE TABLE alg.tabellenenviews
(
sorteren serial NOT NULL,
relid integer,
"type" text,
"schema" name,
tabelnaam name,
eigenaar name,
records_schatting bigint,
grootte_bytes bigint,
omschrijving text,
CONSTRAINT tabellenenviews_pkey PRIMARY KEY (sorteren)
)
WITH (
OIDS=FALSE
);
GRANT SELECT ON TABLE alg.tabellenenviews TO public;
GRANT UPDATE ON TABLE alg.tabellenenviews TO admins;
COMMENT ON TABLE alg.tabellenenviews IS 'Overzicht van alle tabellen en views in de database. (meer commentaar na harde return..)
Pas kolom "omschrijving" aan om commentaar van view of tabel ook aan te passen.
Deze tabel wordt elke nacht automatisch opnieuw gegenereerd.
Laatst geupdate op:2011-10-14 05:00:03.238905';
CREATE OR REPLACE FUNCTION alg.setcomment(p_type text, p_schema name, p_rel name, p_omschrijving text)
RETURNS void AS
$BODY$
DECLARE
--t text;
BEGIN
--t:='COMMENT ON '||replace($1, 'tabel','TABLE')||' '||quote_ident($2)||'.'||quote_ident($3)||' IS '||quote_nullable($4);
--raise notice 'commando is: %', t;
EXECUTE 'COMMENT ON '||replace($1, 'tabel','TABLE')||' '||quote_ident($2)||'.'||quote_ident($3)||' IS '||quote_nullable($4);
END
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION alg.setcomment(text, name, name, text) SET search_path=alg;
COMMENT ON FUNCTION alg.setcomment(text, name, name, text) IS 'wordt gebruikt door de triggerfunctie alg.tabellenviews_setcomment_triggerfunctie() om het commentaar op tabellen te veranderen als dat wordt aangepast in de tabel alg.tabellenenviews';
CREATE OR REPLACE FUNCTION alg.tabellenenviews_setcomment_triggerfunction()
RETURNS trigger AS
$BODY$
BEGIN
PERFORM alg.setcomment(OLD.type, OLD.schema, OLD.tabelnaam, NEW.omschrijving);
NEW.sorteren:=OLD.sorteren;
NEW.relid:=OLD.relid;
NEW."type":=OLD."type";
NEW."schema":=OLD."schema";
NEW.tabelnaam:=OLD.tabelnaam;
NEW.eigenaar:=OLD.eigenaar;
NEW.records_schatting:=OLD.records_schatting;
NEW.grootte_bytes:=OLD.grootte_bytes;
--NEW.omschrijving:=OLD.omschrijving; --deze houdt dus de nieuwe waarde.
RETURN NEW;
END
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
COMMENT ON FUNCTION alg.tabellenenviews_setcomment_triggerfunction() IS 'past het commentaar van een tabel of view aan in de postgres systeemtabellen als dat wordt aangepast in de tabel alg.tabellenviews';
CREATE TRIGGER setcomment_trigger
BEFORE UPDATE
ON alg.tabellenenviews
FOR EACH ROW
EXECUTE PROCEDURE alg.tabellenenviews_setcomment_triggerfunction();
CREATE OR REPLACE VIEW alg.tabellenenviews_mooi AS
SELECT tabellenenviews.type, tabellenenviews.schema, tabellenenviews.tabelnaam, tabellenenviews.eigenaar, tabellenenviews.records_schatting, pg_size_pretty(tabellenenviews.grootte_bytes) AS grootte_mooi, tabellenenviews.omschrijving
FROM alg.tabellenenviews;
GRANT SELECT ON TABLE alg.tabellenenviews_mooi TO public;
COMMENT ON VIEW alg.tabellenenviews_mooi IS 'Overzicht van alle tabellen en views in de database, met tekstuele weergave van de bestandsgrootte van de tabellen';
/*
--this is no use without support for editable views in pgAdmin
CREATE RULE _UPDATE AS ON UPDATE TO alg.tabellenenviews_mooi
DO INSTEAD
UPDATE alg.tabellenenviews
SET omschrijving = NEW.omschrijving
WHERE schema = OLD.schema
AND tabelnaam = OLD.tabelnaam;
GRANT UPDATE ON TABLE alg.tabellenenviews_mooi TO public;
*/
--wordt afgevuurd vanuit de crontab van wbloos (willy-bas)
TRUNCATE alg.tabellenenviews;
INSERT INTO alg.tabellenenviews (relid,type,schema,tabelnaam,eigenaar,records_schatting,grootte_bytes,omschrijving)
SELECT c.oid AS relid
,CASE
WHEN c.relkind = ANY (ARRAY['r'::"char", 't'::"char"]) THEN 'tabel'
WHEN c.relkind = 'v' THEN 'view'
END as type
,n.nspname AS schema, c.relname as tabelnaam, pg_get_userbyid(c.relowner) AS eigenaar
,CASE
WHEN c.relkind = ANY (ARRAY['r'::"char", 't'::"char"]) THEN c.reltuples::bigint
END as records_schatting
,CASE
WHEN c.relkind = ANY (ARRAY['r'::"char", 't'::"char"]) THEN pg_relation_size(quote_ident(n.nspname)||'.'||quote_ident(c.relname))
END as grootte_bytes
,obj_description(c.oid, 'pg_class') as omschrijving
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'v'::"char"])
AND (n.nspname != ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND n.nspname !~ '^pg_toast'::text
ORDER BY "schema", "type", tabelnaam;
--vacuum analyze alg.tabellenenviews; --VACUUM cannot be executed from a function or multi-command string
--aantal records, grootte en comentaar aanpassen voor tabellenviews zelf
--grootte en aantal recs aanpassen van tabellenenviews zelf
alter table alg.tabellenenviews disable trigger setcomment_trigger;
update alg.tabellenenviews tab
set records_schatting = c.reltuples::bigint, grootte_bytes = pg_relation_size('alg.tabellenenviews')
from (select reltuples from pg_class left join pg_namespace n on n.oid = pg_class.relnamespace where n.nspname = 'alg' and pg_class.relname = 'tabellenenviews') c
where tab."schema"='alg' and tab."tabelnaam"='tabellenenviews';
alter table alg.tabellenenviews enable trigger setcomment_trigger;
--commentaar updaten met huidige datum+tijd
update alg.tabellenenviews tab
set omschrijving = 'Overzicht van alle tabellen en views in de database. (meer commentaar na harde return..)
Pas kolom "omschrijving" aan om commentaar van view of tabel ook aan te passen.
Deze tabel wordt elke nacht automatisch opnieuw gegenereerd.
Laatst geupdate op:'||'now'::timestamp::text
where tab."schema"='alg' and tab."tabelnaam"='tabellenenviews';
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
