PGSQL统计数据库所有表字段为空的函数实现

-- Function: findnull()

-- DROP FUNCTION findnull();

CREATE OR REPLACE FUNCTION findnull()
  RETURNS boolean AS
$BODY$
DECLARE
        ex character varying(1000);
        rec RECORD;
        rec2 RECORD;
        rec3 RECORD;
BEGIN
DROP TABLE IF EXISTS "TempTable"  CASCADE;
CREATE TEMP TABLE "TempTable" (
"tablename" character varying(50),
"attname" character varying(50) ,
"NumberOfNull" bigint );
FOR rec IN SELECT "tablename" FROM "pg_tables" WHERE "schemaname" ='public' ORDER BY "tablename" LOOP
FOR rec2 IN SELECT a."attname" FROM "pg_attribute" AS a,"pg_type" AS b WHERE a."attstattarget" = -1
AND b."typrelid" = a."attrelid"  AND b."typname" = rec."tablename"ORDER BY a."attname" LOOP
ex = 'SELECT COUNT(0) FROM "'||rec."tablename"||'" WHERE "'||rec2."attname"||'" IS NULL';
FOR rec3 IN execute (ex) LOOP
INSERT INTO "TempTable" VALUES(rec."tablename",rec2."attname",rec3."count");
END LOOP;
END LOOP;
END LOOP;
RETURN TRUE;
END;
$BODY$