一、 postgres使用函数批量删除数据万级数据

CREATE or REPLACE FUNCTION del_logs_datas() RETURNS text as 
$$
		DECLARE
			b_count int;

			 BEGIN
				b_count :=  10;
				while b_count > 0 LOOP
					DELETE FROM logs where time in (SELECT time FROM logs LIMIT 900000 OFFSET 0);
					b_count := b_count - 1;
				END LOOP;
				RETURN '删除成功！';
			 END;
$$
LANGUAGE plpgsql;

-- 执行分段批量删除
SELECT del_logs_datas();

二 、 postgres使用函数批量插入数据

CREATE or REPLACE FUNCTION insert_many() RETURNS text as 
$$
		DECLARE
		  r int;
			b_count int;
      insert_name VARCHAR;
			 BEGIN
				b_count := 22222;
				while b_count < 22299 LOOP
			  	 r  := (random() * 9000000)::INT;
					insert into test_effects(id,name) VALUES(b_count,r);
					b_count := b_count + 1;
				END LOOP;
				RETURN '插入成功';
			 END;
$$
LANGUAGE plpgsql;
 
-- 执行分段批量删除
SELECT insert_many();