含动态COPY语句的PostgreSQL函数执行报错及自动执行需求

错误原因分析 #

你的函数存在两个核心问题：

1. 函数声明返回VARCHAR(1000)，但函数体中没有RETURN语句返回对应类型的数据，PL/pgSQL要求有返回值的函数必须明确返回结果。
2. 动态COPY语句的字符串拼接错误：在EXECUTE的字符串中，变量vtoday直接用||连接不会被解析为变量值，而是作为字符串的一部分，导致生成的COPY路径无效，同时写法不符合动态SQL规范。

修正后的函数代码 #

CREATE OR REPLACE FUNCTION loadTodaysData()
RETURNS VARCHAR(1000) AS $$
DECLARE 
    vtoday VARCHAR(20) DEFAULT CURRENT_DATE::TEXT;
    copy_sql TEXT;
BEGIN
    -- 使用format函数安全拼接动态SQL，避免语法错误和SQL注入
    copy_sql := format(
        'COPY nsn FROM %L WITH DELIMITER '','' CSV HEADER',
        'C:\Users\yuju\SQL_Data\test_' || vtoday || '.csv'
    );
    
    EXECUTE copy_sql;
    
    -- 返回成功提示
    RETURN '数据加载完成，加载文件：test_' || vtoday || '.csv';
EXCEPTION
    WHEN OTHERS THEN
        -- 捕获异常并返回错误信息
        RETURN '数据加载失败：' || SQLERRM;
END; $$
LANGUAGE plpgsql;

关键修正点说明 #

• 使用format函数处理动态SQL：%L会自动将路径字符串转义为符合PostgreSQL语法的字符串常量，避免手动拼接单引号的麻烦，同时更安全。
• 增加RETURN语句：无论是执行成功还是异常，都返回对应的字符串结果，满足函数的返回值要求。
• 增加异常处理：捕获执行过程中的错误，返回具体错误信息，方便排查问题。
• 将CURRENT_DATE转为TEXT类型：确保日期格式和字符串拼接的兼容性。

每日自动更新实现方案 #

要实现每日自动执行该函数，可借助PostgreSQL的pg_cron扩展（需先安装）：

1. 安装pg_cron扩展（以PostgreSQL 12+为例）：

CREATE EXTENSION IF NOT EXISTS pg_cron;

2. 创建每日定时任务（比如每天凌晨2点执行）：

SELECT cron.schedule(
    'daily-load-nsn-data', -- 任务名称
    '0 2 * * *', -- cron表达式：每天凌晨2点
    'SELECT loadTodaysData();' -- 执行的SQL语句
);

内容的提问来源于stack exchange，提问作者Yuju Takahashi