1.创建数据库
psql
create database db_hxl encoding = 'utf8';
#\c db_hxl
2.创建表
create table tb_test01
(
id bigserial primary key not null,
name varchar(32),
createtime timestamp default current_timestamp,
modifytime timestamp default current_timestamp
);
3.创建存储过程
CREATE OR REPLACE FUNCTION "public"."sp_insert_data"() RETURNS "pg_catalog"."int4" AS $BODY$ declare total integer; i integer := 1; BEGIN while i <= 1000 loop insert into tb_test01(name) values('name'||i); update tb_test01 set name='更新' ,modifytime=now() where id=ceil(random()*(10000-1)+1); i=i+1; end loop; RETURN 1; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100
4.创建crontab 调度
[postgres@localhost scripts]$ crontab -l
##pg生成测试数据
*/30 * * * * /home/postgres/scripts/insert_data.sh 1>/dev/null 2>&1
[postgres@localhost scripts]$ more insert_data.sh
#!/bin/bash
. /etc/profile
. ~/.bash_profile
echo a>>/tmp/a.txt
psql -U postgres -h localhost -d db_hxl << EOF
select sp_insert_data();
EOF