如果你只是玩玩小数据库，手动跑query或者procedure来做报表其实没啥大不了的。但现实里数据库会变得很大，这时候所有重复的活都得自动化。想象下，每天都有人让你做销售报表。哪怕query只要两分钟，一年下来你也得花超过12小时在这上面。还不如让自动procedure帮你搞定，你去喝咖啡多爽。
自动化能帮你：
- 减少手动操作。
- 保证报表定时生成（比如每天、每周的报表）。
- 把因为人为失误导致的bug降到最低。
- 让大家更信任你的报表：都是按固定参数自动生成的。
自动生成报表的主要步骤
自动跑报表一般分这几步：
- 用PL/pgSQL写一个procedure，专门生成报表。
- 设置结果日志（如果需要的话）。
- 用任务调度器定时跑procedure。
来，咱们一步步搞起来！
写一个生成报表的procedure
先来个简单的procedure，算一下今天所有订单的总销售额，然后把结果存到日志表里。日志表我们已经有了（就叫sales_report_log）：
CREATE TABLE sales_report_log (
report_date DATE NOT NULL,
total_sales NUMERIC NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
现在写个PL/pgSQL的procedure：
CREATE OR REPLACE FUNCTION generate_daily_sales_report()
RETURNS VOID AS $$
BEGIN
-- 统计今天的总销售额
INSERT INTO sales_report_log (report_date, total_sales)
SELECT CURRENT_DATE, SUM(order_total)
FROM orders
WHERE order_date = CURRENT_DATE;
RAISE NOTICE '今天的报表 % 已经生成', CURRENT_DATE;
END;
$$ LANGUAGE plpgsql;
这里发生了啥：
- 我们用
SUM()聚合函数从orders表里算总销售额。 - 报表日期（
report_date）就是今天（CURRENT_DATE）。 - 结果存到
sales_report_log表里。 - 加了个
RAISE NOTICE消息方便调试：它会告诉你报表已经生成。
测试procedure
在自动化之前，最好手动测一下。跑一下function：
SELECT generate_daily_sales_report();
然后查查sales_report_log表内容：
SELECT * FROM sales_report_log;
如果你看到有今天的日期和正确的总销售额——恭喜，function没问题！
在PostgreSQL里自动化任务
有时候让数据库自己干点活挺爽的：比如自动跑报表、清理旧数据或者定时更新聚合。PostgreSQL可以用pg_cron扩展或者外部任务调度器——系统的cron或者Task Scheduler来搞定。
如果你用的是Linux，最推荐用pg_cron。这个扩展能直接在PostgreSQL里跑SQL，不用shell或者脚本。
装pg_cron的方法（记得把XX换成你自己的PostgreSQL版本）：
sudo apt install postgresql-XX-cron
装完后要在配置里加上它。打开postgresql.conf，加一行：
shared_preload_libraries = 'pg_cron'
然后重启PostgreSQL，在你的数据库里激活扩展：
CREATE EXTENSION pg_cron;
现在可以定时任务了。比如每天零点跑generate_daily_sales_report()：
SELECT cron.schedule(
'daily_sales_report',
'0 0 * * *',
$$ SELECT generate_daily_sales_report(); $$
);
这里：
'daily_sales_report'—— 任务名；'0 0 * * *'—— cron格式的时间（这里是每天00:00）；- 中间
$$里的SQL就是要执行的代码。
想看所有定时任务，用：
SELECT * FROM cron.job;
如果你用Windows或者macOS，pg_cron要么根本不支持（Windows），要么得自己编译源码（macOS）。挺麻烦的，大多数时候直接用系统调度器更省事。
方法如下：
- 先写个SQL文件，里面是你要跑的命令：
echo "SELECT generate_daily_sales_report();" > /path/to/script.sql
- 用
psql执行这个文件：
psql -h localhost -U postgres -d your_database -f /path/to/script.sql
把这个命令加到任务调度器里：
在Linux/macOS下：用
crontab -e：0 0 * * * psql -h localhost -U postgres -d your_database -f /path/to/script.sql在Windows下：用Task Scheduler新建任务，跑
psql.exe，参数自己填。
- 如果你用Linux，直接用
pg_cron，又方便又集成。 - 如果你用Windows或者Mac，用系统调度器（
cron或者Task Scheduler）+psql跑SQL就行。
这样你就能轻松自动化PostgreSQL里的各种任务了。
自动报表的例子
- 每天按地区的报表
比如你想自动生成每个地区的销售额报表，可以扩展一下我们的function：
CREATE OR REPLACE FUNCTION generate_regional_sales_report()
RETURNS VOID AS $$
BEGIN
INSERT INTO regional_sales_report_log (region, report_date, total_sales)
SELECT region, CURRENT_DATE, SUM(order_total)
FROM orders
WHERE order_date = CURRENT_DATE
GROUP BY region;
RAISE NOTICE '地区报表 % 已经生成', CURRENT_DATE;
END;
$$ LANGUAGE plpgsql;- 每月报表
类似地，也可以写个procedure生成月报。只要改一下query里的过滤条件：
WHERE order_date BETWEEN date_trunc('month', CURRENT_DATE)
AND date_trunc('month', CURRENT_DATE) + interval '1 month - 1 day';
常见错误和怎么避免
自动生成报表时可能会遇到这些坑：
- function里写错语法：自动化前一定要手动测一遍function。
- 任务跑得太频繁：如果任务太密集，数据库可能扛不住。合理设置定时。
- 数据重复：一天跑多次报表可能会有重复。用唯一键防止重复插入。
这节讲座教你怎么在PostgreSQL里自动生成报表。现在你可以优化自己的分析流程，把时间省下来干点更重要的事……比如找bug、写代码，或者幻想你的SQL能完美无瑕。
GO TO FULL VERSION