postgresql 统计一张表每个字段不为NULL和空值的数量

创建一张表：

create table test_table(
    year int,
    m01 int,
    m02 int,
    m03 int,
    m04 int
);
insert into test_table values(2018, 1,2,3,4);
insert into test_table values(2019, 10,20,30,null);
insert into test_table values(2020, 100,200,null,'');

表数据：

year	m01	m02	m03	m04
2018	1	2	3	4
2019	10	20	30	null
2020	100	200	null

创建一个函数，SQL代码：

CREATE OR REPLACE FUNCTION "public"."get_film"("database1" varchar, "table1" varchar)
  RETURNS TABLE("database" text, "table" text, "column" text, "sum" int8, "ratio" text) AS $BODY$
declare
v_msg character varying :='';
v_msg1 character varying :='';
course refcursor;
BEGIN
if not exists(select 1 from information_schema.columns
where table_schema=''||database1||'' and table_name=''||table1||'')
then
RAISE EXCEPTION '请输入正确的库名和表名！';
end if;
open course for execute 'select column_name
from information_schema.columns
where table_schema='''||database1||''' and table_name='''||table1||'''
order by ordinal_position;';
loop
fetch course into v_msg1;
if found then
v_msg :=v_msg||'select '''||database1||''' as database1,'''||table1||''' as table1,'''||v_msg1||''' as column1,
sum(case when '||v_msg1||' is null or '||v_msg1||'::varchar ='''' then 0 else 1 end) as sum1,
round(sum(case when '||v_msg1||' is null or '||v_msg1||'::varchar ='''' then 0 else 1 end)/count(*)::numeric*100,2)||''%'' as ratio1
from '||database1||'.'||table1||' group by 1
union all'||chr(10);
        else
            exit;
        end if;
end loop;
close course;
v_msg :=v_msg||'select '''||database1||''' as database1,'''||table1||''' as table1,''count_sum'' as column1,
count(*) as sum1,''100.00%'' as ratio1
from '||database1||'.'||table1||' group by 1';
return query execute v_msg;
END; $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000

调用：

select * from public.get_film('public','test_table')

查询结果：

database	table	column	sum	ratio
public	test_table	year	3	100.00%
public	test_table	m01	3	100.00%
public	test_table	m02	3	100.00%
public	test_table	m03	2	66.67%
public	test_table	m04	1	33.33%
public	test_table	count_sum	3	100.00%