CronJob执行数据库里的函数入门

本文将介绍如何用定时任务，执行postgresql数据库里的函数。

1. 创建一个student表

create table public.student
(
    id   integer not null
        constraint student_pk
            primary key,
    name varchar,
    age  integer,
    sex  integer
);

alter table public.student
    owner to postgres;

2. 插入初始化数据
   
3. 创建一个函数

create or replace function f_delete(table_name character, id integer)
    returns void
    language plpgsql
    security definer
as
$function$
    declare
        v_sqlstate text;
        v_message text;
        v_context text;
        stmt text;
begin
    stmt:= 'delete from ' || table_name || ' where id = ' || id;
    raise notice '%',stmt;
    execute stmt;
    raise notice 'delete id % from % successfully',id,table_name;
exception
    when others then
        GET STACKED DIAGNOSTICS
            v_sqlstate = returned_sqlstate,
            v_message = message_text,
            v_context = pg_exception_context;
        RAISE NOTICE 'sqlstate: %', v_sqlstate;
        RAISE NOTICE 'message: %', v_message;
        RAISE NOTICE 'context: %', v_context;
end;
$function$;

这个函数的功能很简单，删除表中指定ID的数据

4. 创建delete_student.sh,将以下内容复制到文件中

#!/bin/bash

/usr/local/bin/docker exec -t a4519c4b4a1cf8c8cc5aee83d9e8315304f547f6d5710bf9ceabfdff1a0e64de psql -U postgres -d postgres -c "SELECT f_delete('student',1)"

或者

#!/bin/bash

export PATH="$PATH:/usr/bin"  # 替换成你的 docker 命令路径
docker exec -t a4519c4b4a1cf8c8cc5aee83d9e8315304f547f6d5710bf9ceabfdff1a0e64de psql -U postgres -d postgres -c "SELECT f_delete('student',1)"

因为我是通过容器启动的postgresql数据库，所以我需要提供容器的id和数据库用户名。这里我删除student表中id为1的数据。

5. 给delete_student.sh赋予执行权限

chmod 777 delete_student.sh

6. 创建定时任务

编辑 crontab 文件： 在你的 macOS 终端中运行以下命令以编辑当前用户的 crontab 文件：

 crontab -e

然后输入i进入编辑模式，复制以下脚本，修改为对应你的值

* * * * * /Users/mamingcong/Public/testDir/delete_student.sh >> /Users/mamingcong/Public/testDir/output.log 2>&1

这里我每分钟运行一下这个函数，并且将所有的日志输出到指定的文件夹。

7. 查看创建的定时任务

mamingcong@SimonMa testDir % crontab -l
* * * * * /Users/mamingcong/Public/testDir/delete_student.sh >> /Users/mamingcong/Public/testDir/output.log 2>&1

8. 查看执行结果
   查看日志文件可以得到执行结果,当然你也可以直接查看数据库里的数据。

mamingcong@SimonMa testDir % output.log
NOTICE:  delete from student where id = 1
NOTICE:  delete id 1 from student successfully
 f_delete 
----------
 
(1 row)