
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';
