-- Author  : Stefan van der Berg (svb@ucs.co.za) 
-- Purpose : Takes table name as parameter and returns an md5sum
--           generated from all objects attached to a table.
create or replace function get_table_checksum(text) returns text
as '
declare
	table       alias for $1;
        search      text;
        taboid      text;
	q           text;
	qa          text;
        tab_rec     record;
        def_rec     record;
        hasindex    boolean;
        hasrules    boolean;
        hasindext   text;
        hasrulest   text;
        notnull      boolean;
        hasdef       boolean;
        notnullt     text;
        hasdeft      text;
        isprimary    boolean;
        isunique     boolean;
        isprimaryt   text;
        isuniquet    text;
        md5sum       text;
begin
        md5sum := \'NO_SUCH_TABLE\';
        taboid := null;
        search := \'^\' || table || \'$\';
	for tab_rec in SELECT c.oid as oid,n.nspname as nspname ,c.relname as relname
                       FROM pg_catalog.pg_class c
                           LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
                       WHERE pg_catalog.pg_table_is_visible(c.oid)
                       AND c.relname ~ search
                       ORDER BY 2, 3
        loop
           taboid := tab_rec.oid;
           qa     := tab_rec.nspname || \':\' || tab_rec.relname;
        end loop;

        if taboid is null
        then
           return md5sum;
        end if;

	for tab_rec in SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules
                       FROM pg_catalog.pg_class WHERE oid = taboid
        loop
           hasindex := tab_rec.relhasindex;
           hasrules := tab_rec.relhasrules;
           hasindext := \'f\';
           if hasindex
           then 
              hasindext := \'t\';
           end if;
           hasrulest := \'f\';
           if hasrules
           then 
              hasrulest := \'t\';
           end if;
           qa := qa || \':\' || hasindext || \':\' || tab_rec.relkind ||  \':\' || tab_rec.relchecks;
           qa := qa || \':\' || tab_rec.reltriggers || \':\' || hasrulest;
        end loop;

	for tab_rec in SELECT a.attname as attname, pg_catalog.format_type(a.atttypid, a.atttypmod) as atttypid,
                              a.attnotnull as attnotnull, a.atthasdef as atthasdef, a.attnum as attnum
                       FROM pg_catalog.pg_attribute a
                       WHERE a.attrelid = taboid AND a.attnum > 0 AND NOT a.attisdropped
                       ORDER BY a.attnum
        loop
           notnull  := tab_rec.attnotnull;
           hasdef   := tab_rec.atthasdef;
           notnullt := \'f\';
           if notnull
           then 
              notnullt := \'t\';
           end if;
           hasdeft := \'f\';
           if hasdef
           then 
              hasdeft := \'t\';
           end if;
           qa := qa || \':\' || tab_rec.attname || \':\' || tab_rec.atttypid ||  \':\' || notnullt;
           qa := qa || \':\' || hasdeft || \':\' || tab_rec.attnum;
        end loop;

	for tab_rec in SELECT a.attnum as attnum
                       FROM pg_catalog.pg_attribute a
                       WHERE a.attrelid = taboid AND a.attnum > 0 AND NOT a.attisdropped and a.atthasdef = true
                       ORDER BY a.attnum
        
        loop
           qa := qa || \':\' || tab_rec.attnum;
           for def_rec in SELECT coalesce(nullif(coalesce(nullif(replace(replace(substring(d.adsrc for 128),\'\\\'\',\'\'),\'::text\',\'\'),\'t\'),\'true\'),\'f\'),\'false\') as def FROM pg_catalog.pg_attrdef d
                          WHERE d.adrelid = taboid AND d.adnum = tab_rec.attnum
           loop
               qa := qa || \':\' || def_rec.def;
           end loop;
        end loop;

	for tab_rec in SELECT c2.relname, i.indisprimary, i.indisunique, pg_catalog.pg_get_indexdef(i.indexrelid) as indexrelid
                       FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i
                       WHERE c.oid = taboid AND c.oid = i.indrelid AND i.indexrelid = c2.oid 
                       ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname
        loop
           isprimary  := tab_rec.indisprimary;
           isunique   := tab_rec.indisunique;
           isprimaryt := \'f\';
           if isprimary
           then 
              isprimaryt := \'t\';
           end if;
           isuniquet := \'f\';
           if isunique
           then 
              isuniquet := \'t\';
           end if;
           qa := qa || \':\' || tab_rec.relname || \':\' || isprimaryt ||  \':\' || isuniquet;
           qa := qa || \':\' || tab_rec.indexrelid;
        end loop;

	for tab_rec in SELECT t.tgname
                       FROM pg_catalog.pg_trigger t
                       WHERE t.tgrelid = taboid
                       AND (not tgisconstraint  OR NOT EXISTS  (SELECT 1 FROM
                            pg_catalog.pg_depend d JOIN pg_catalog.pg_constraint c ON
                            (d.refclassid = c.tableoid AND d.refobjid = c.oid) 
                             WHERE d.classid = t.tableoid AND d.objid = t.oid AND d.deptype = \'i\' AND
                             c.contype = \'f\'))
                       ORDER BY t.tgname
        loop
           qa := qa || \':\' || tab_rec.tgname;
        end loop;

	for tab_rec in SELECT conname, substring(pg_catalog.pg_get_constraintdef(oid) from \'^(.*\\\\\\51)[ ]?[O]?[N]?\') as condef
                       FROM pg_catalog.pg_constraint r
                       WHERE r.conrelid = taboid AND r.contype = \'f\'
                       ORDER BY conname
        loop
           qa := qa || \':\' || tab_rec.conname || \':\' || tab_rec.condef;
        end loop;

	for tab_rec in SELECT encode(digest(qa, \'md5\'),\'hex\') as md5
        loop
           md5sum := tab_rec.md5;
        end loop;

        return md5sum;
end; ' LANGUAGE 'plpgsql' ;
