如何从PostgreSQL表生成JSON文件并将参数作为导出文件名

基础导出实现（COPY TO语法） #

PostgreSQL原生支持通过COPY TO语法直接将查询结果导出为JSON格式，两种常用导出格式示例如下：

• 导出为完整JSON数组：

COPY (
  SELECT json_agg(表别名) FROM 目标表名 表别名
) TO '/var/lib/postgresql/sftp/smrptesting1.json';

• 导出为JSON Lines格式（每行一个独立JSON对象，适合大文件导出）：

COPY (
  SELECT to_json(表别名) FROM 目标表名 表别名
) TO '/var/lib/postgresql/sftp/smrptesting1.jsonl';

函数中使用动态文件名解决方案 #

你遇到的无法将参数作为文件名使用的问题，是因为原生COPY语句不支持直接传入变量作为路径参数，需要在PL/pgSQL函数中使用动态SQL拼接语句实现，示例代码如下：

CREATE OR REPLACE FUNCTION export_json_by_filename(p_filename text)
RETURNS void AS $$
BEGIN
  -- 使用format函数的%L占位符自动转义文件名，避免SQL注入
  EXECUTE format(
    'COPY (SELECT json_agg(t) FROM 你的目标表 t) TO %L',
    -- 拼接固定目录和传入的文件名参数
    '/var/lib/postgresql/sftp/' || p_filename
  );
  -- 如果要导出的是自定义变量v_originaltext对应的内容，只需调整COPY后的查询语句为你生成v_originaltext的逻辑即可
END;
$$ LANGUAGE plpgsql VOLATILE;

调用示例 #

-- 传入文件名参数即可导出到对应路径
SELECT export_json_by_filename('smrptesting1.json');

注意事项 #

• 服务端COPY TO需要PostgreSQL运行用户对目标目录拥有写入权限，请勿直接使用root用户或其他普通用户的专属目录
• 动态拼接SQL时必须使用转义占位符处理文件名参数，禁止直接拼接字符串防止SQL注入风险
• 定时调度可以结合系统定时任务（如Linux的crontab）实现，示例定时任务命令如下：
  0 1 * * * psql -U 数据库用户名 -d 目标库名 -c "SELECT export_json_by_filename('daily_export_'||current_date||'.json');"
• 如果没有数据库超级用户权限，可以使用客户端\copy命令执行导出，不需要服务端目录权限，示例命令：
  psql -U 数据库用户名 -d 目标库名 -c "\copy (SELECT json_agg(t) FROM 目标表 t) TO '/本地用户可写目录/export.json'"

内容的提问来源于stack exchange，提问作者Yamin Azlan