PostgreSQL 使用存储过程每日统计数据库所有模式下的每张表的数量

目录

1.建统计结果表

 2.获取 模式.表名 的sql

 3.使用游标编写存储过程

 附SQL语句

---

1.建统计结果表

 2.获取 模式.表名 的sql

SELECT
    table_schema || '.' || TABLE_NAME AS schema_table_name 
FROM
    information_schema.tables;

 3.使用游标编写存储过程

--存储过程语句
CREATE OR REPLACE FUNCTION "public"."table_statistics"()
  RETURNS "pg_catalog"."int4" AS $BODY$

-- 定义变量     
DECLARE 
        -- 等下接收 计算的数据量
        table_rows int  ;
        
        -- 等下接收 查询出来的 模式.表名
        my_table_name VARCHAR(1000);
        
    -- 定义游标 并将sql语句查询出来的 模式.表名 赋值给 table_cursor
        table_cursor CURSOR FOR select table_schema||'.'||table_name as schema_table_name from information_schema.tables ;
        
BEGIN
    -- 开启游标
    OPEN table_cursor;
    
    --删除昨天统计数据
    delete from public.t_data_lake_statis where statis_date >= CURRENT_DATE-1;
    
    --循环遍历游标
    LOOP
    -- 游标中取到表名赋值给变量my_table_name
    FETCH table_cursor INTO my_table_name;
    Exit when NOT found;
    -- 将表的数据量赋值给 table_rows 
        EXECUTE  'select count(*) from '||my_table_name || ' ' INTO table_rows; 
        --将得到的数据插入到数据表中
         insert into 
            public.T_DATA_LAKE_STATIS (statis_date,schema_name,schema_table_name,table_name,table_rows,collect_time) 
        VALUES 
            (CURRENT_DATE-1,SPLIT_PART( my_table_name, '.', 1 ),my_table_name,SPLIT_PART( my_table_name, '.', 2 ),table_rows,now());
    END LOOP;
    --关闭游标
    CLOSE table_cursor;
--返回值，随便写的
return 1;

end
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

 附SQL语句

-- 统计信息表 建表语句
CREATE TABLE public.t_data_lake_statis (
  statis_date date NOT NULL,
  schema_name varchar(255) COLLATE pg_catalog.default NOT NULL,
  schema_table_name varchar(255) COLLATE pg_catalog.default NOT NULL,
  table_name varchar(255) COLLATE pg_catalog.default NOT NULL,
  table_rows numeric(255,0),
  collect_time timestamp(0)
)
;

ALTER TABLE public.t_data_lake_statis 
  OWNER TO gpadmin;

COMMENT ON COLUMN public.t_data_lake_statis.statis_date IS '主键ID';

COMMENT ON COLUMN public.t_data_lake_statis.schema_name IS 'Schema名称';

COMMENT ON COLUMN public.t_data_lake_statis.schema_table_name IS 'schema+表名称';

COMMENT ON COLUMN public.t_data_lake_statis.table_name IS '表名称';

COMMENT ON COLUMN public.t_data_lake_statis.table_rows IS '表数据行数';

COMMENT ON COLUMN public.t_data_lake_statis.collect_time IS '数据采集时间';

COMMENT ON TABLE public.t_data_lake_statis IS '数据湖表行数日统计表';


--存储过程语句
CREATE OR REPLACE FUNCTION "public"."table_statistics"()
  RETURNS "pg_catalog"."int4" AS $BODY$

-- 定义变量 	
DECLARE 
		-- 等下接收 计算的数据量
		table_rows int  ;
		
		-- 等下接收 查询出来的 模式.表名
		my_table_name VARCHAR(1000);
		
	-- 定义游标 并将sql语句查询出来的 模式.表名 赋值给 table_cursor
		table_cursor CURSOR FOR select table_schema||'.'||table_name as schema_table_name from information_schema.tables ;
		
BEGIN
	-- 开启游标
	OPEN table_cursor;
	
	--删除昨天统计数据
	delete from public.t_data_lake_statis where statis_date >= CURRENT_DATE-1;
	
	--循环遍历游标
	LOOP
	-- 游标中取到表名赋值给变量my_table_name
	FETCH table_cursor INTO my_table_name;
	Exit when NOT found;
	-- 将表的数据量赋值给 table_rows 
		EXECUTE  'select count(*) from '||my_table_name || ' ' INTO table_rows; 
		--将得到的数据插入到数据表中
		 insert into 
			public.T_DATA_LAKE_STATIS (statis_date,schema_name,schema_table_name,table_name,table_rows,collect_time) 
		VALUES 
			(CURRENT_DATE-1,SPLIT_PART( my_table_name, '.', 1 ),my_table_name,SPLIT_PART( my_table_name, '.', 2 ),table_rows,now());
	END LOOP;
	--关闭游标
	CLOSE table_cursor;
--返回值，随便写的
return 1;

end
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100