Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- Test tables
CREATE TABLE texte(
  texte_id serial PRIMARY KEY              -- some typical columns, not character types
, day   date NOT NULL DEFAULT now()::date
, ts    timestamp NOT NULL DEFAULT now()
, "oDD name" text DEFAULT ''               -- leaving this one empty in all rows
, txtnn text NOT NULL
, txt   text
, vc    varchar
, vc10  varchar(10)
, c10   char(10)
, c1    char
, dummy text[]                             -- array type will be ignored
);

-- test data
INSERT INTO texte (txtnn, txt, vc, vc10, c10, c1, dummy)
SELECT 'x', 'x', 'x', 'x', 'x', 'x', '{x}'
FROM   generate_series(1,10);              -- regular rows with all but "oDD name" filled

INSERT INTO texte (txtnn, txt, vc, vc10, c10, c1, dummy)
SELECT 'x', 'x', '', '', NULL, 'x', '{x}'
FROM   generate_series(1,10);              -- more colums with some empty

INSERT INTO texte (txtnn, txt, vc, vc10, c10, c1, dummy)
SELECT 'x', 'x', 'x', 'x', 'x', '', '{x}'
FROM   generate_series(1,10);              -- more colums with others empty


-- other table with character types - must show up!
CREATE TABLE other_text(
  notext_id serial PRIMARY KEY              -- some typical columns, not character types
, day   date
, txt   text
);

INSERT INTO other_text (txt)
SELECT ''
FROM   generate_series(1,22);


-- table without character types - must *not* show up!
CREATE TABLE notext(
  notext_id serial PRIMARY KEY              -- some typical columns, not character types
, day   date
, ts    timestamp
);

INSERT INTO notext (ts)
SELECT now()::timestamp
FROM   generate_series(1,10);

CREATE TABLE



INSERT 0 10



INSERT 0 10



INSERT 0 10



CREATE TABLE



INSERT 0 22



CREATE TABLE



INSERT 0 10


-- Function for one table
CREATE OR REPLACE FUNCTION f_tbl_empty_status(_tbl regclass)
  RETURNS TABLE (tbl text, col text, empty_ct bigint, not_null bool)
  LANGUAGE plpgsql AS
$func$
DECLARE
   _typ      CONSTANT regtype[] := '{text, bpchar, varchar}';  -- basic char types
   _sql      text;
   _col_arr  text[];
   _null_arr bool[];
BEGIN
   -- Build command
   SELECT INTO _col_arr, _null_arr, _sql
          array_agg(s.col)
        , array_agg(s.attnotnull)
        , '
   SELECT $1
        , unnest($2)
        , unnest(ARRAY [count('
                 || string_agg(s.col, ' = '''' OR NULL), count(')
                                   || ' = '''' OR NULL)])
        , unnest($3)
   FROM   ' || _tbl
   FROM  (
      SELECT quote_ident(attname) AS col, attnotnull
      FROM   pg_attribute
      WHERE  attrelid = _tbl           -- valid, visible, legal table name 
      AND    attnum >= 1               -- exclude tableoid & friends
      AND    NOT attisdropped          -- exclude dropped columns
   -- AND    NOT attnotnull            -- include columns defined NOT NULL
      AND    atttypid = ANY(_typ)      -- only character types
      ORDER  BY attnum
      ) AS s;

   -- Debug
   -- RAISE NOTICE '%', _sql;

   -- Execute
   IF _sql IS NULL THEN
      -- do nothing, nothing to return
   ELSE
      RETURN QUERY EXECUTE _sql
      USING  _tbl::text, _col_arr, _null_arr;
   END IF;
END
$func$;

CREATE FUNCTION


-- Wrapper for all tables of a given schema
CREATE OR REPLACE FUNCTION f_schema_empty_status(_sch text DEFAULT 'public')
  RETURNS TABLE (tbl text, col text, empty_ct bigint, not_null bool)
  LANGUAGE plpgsql AS
$func$
DECLARE	
   _tbl regclass;
BEGIN
   FOR _tbl IN
      SELECT c.oid
      FROM   pg_class c
      JOIN   pg_namespace n ON n.oid = c.relnamespace
      WHERE  n.nspname = _sch  -- 'public' by default
   -- AND    c.relname LIKE 'my_pattern%'  -- optionally filter table names
      AND    c.relkind = 'r'  -- regular tables only
      ORDER  BY relname
   LOOP
   -- Debug
   -- RAISE NOTICE 'table: %', _tbl;

      RETURN QUERY
      SELECT * FROM f_tbl_empty_status(_tbl);
   END LOOP;
END
$func$;

CREATE FUNCTION


-- Call for 1 table
SELECT * FROM f_tbl_empty_status('texte');


tbl
col
empty_ct
not_null


texte
"oDD name"
30
f

texte
txtnn
0
t

texte
txt
0
f

texte
vc
10
f

texte
vc10
10
f

texte
c10
0
f

texte
c1
10
f



SELECT 7


-- Call for schema (defaults to 'public')
SELECT * FROM f_schema_empty_status();


tbl
col
empty_ct
not_null


other_text
txt
22
f

texte
"oDD name"
30
f

texte
txtnn
0
t

texte
txt
0
f

texte
vc
10
f

texte
vc10
10
f

texte
c10
0
f

texte
c1
10
f



SELECT 8
tbl	col	empty_ct	not_null
texte	"oDD name"	30	f
texte	txtnn	0	t
texte	txt	0	f
texte	vc	10	f
texte	vc10	10	f
texte	c10	0	f
texte	c1	10	f
tbl	col	empty_ct	not_null
other_text	txt	22	f
texte	"oDD name"	30	f
texte	txtnn	0	t
texte	txt	0	f
texte	vc	10	f
texte	vc10	10	f
texte	c10	0	f
texte	c1	10	f