PostgreSQL 9.6.11 datetime异常捕获失效问题求助

我来帮你搞定这个问题，核心有两个关键点导致你的代码没按预期捕获异常，咱们一步步修正：

问题根源分析 #

1. 参数转换错误发生在函数执行前：你的函数参数Column3定义为timestamp without time zone，当你传入错误格式的字符串时，PostgreSQL会先尝试把字符串转换成timestamp类型，这个转换过程发生在函数开始执行之前，所以函数内部的EXCEPTION块根本没机会捕获这个错误。
2. 用了PostgreSQL不存在的异常名：invalid_datetime_format是Oracle PL/SQL里的异常名，PostgreSQL的PL/pgSQL并没有这个内置标识，对应时间戳格式错误的SQLSTATE是22007，也可以用条件名invalid_text_representation。

修正后的解决方案 #

我们需要调整函数参数类型，并修正异常捕获逻辑，具体代码如下：

步骤1：修改函数定义 #

CREATE OR REPLACE FUNCTION test_schema.test_function(
 Column1 character varying,
 Column2 date,
 Column3 text) -- 改为text类型，让转换操作在函数内部执行
RETURNS character varying
LANGUAGE 'plpgsql'
COST 100
VOLATILE
AS $BODY$
declare
 isExists boolean;
 message character varying;
 err_context text;
 converted_timestamp timestamp without time zone; -- 存储转换后的合法时间戳
begin
 SET timezone TO 'America/New_York';
 
 -- 先尝试转换时间戳，格式错误会在这里抛出异常
 converted_timestamp := Column3::timestamp without time zone;
 
 insert into test_schema.test_table (
 Column1, Column2, Column3, Column4
 ) values (
 Column1, Column2, converted_timestamp, 'xyz'
 );
 return 'Successful';

-- 处理异常逻辑
EXCEPTION
 WHEN SQLSTATE '22007' THEN -- 捕获时间戳格式错误对应的SQLSTATE
 message := 'Datetime format is not valid' || E'\n' || '系统错误信息: ' || SQLERRM;
 return message;
 WHEN OTHERS THEN
 GET STACKED DIAGNOSTICS err_context = PG_EXCEPTION_CONTEXT;
 message := 'Error: '||SQLERRM||E'\n'||err_context;
 return message;
end;
$BODY$;

步骤2：测试验证 #

调用函数时传入错误格式的时间字符串：

SELECT test_schema.test_function('test_val', '2019-11-10', '2019-11-10-07.10.55.865000');

此时函数会返回你预期的自定义错误信息：

Datetime format is not valid
系统错误信息: invalid input syntax for type timestamp: "2019-11-10-07.10.55.865000"

额外说明 #

如果你更习惯用条件名而非SQLSTATE，可以把WHEN SQLSTATE '22007'替换成WHEN invalid_text_representation，两者效果完全一致，因为invalid_text_representation是PostgreSQL为SQLSTATE 22007定义的官方条件名。

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