如何在PostgreSQL中创建正确检查数据类型兼容性的函数

你的函数失效的核心原因是无法直接用参数字符串作为类型名进行强制转换，d::t这种写法里，PostgreSQL会把t当成字面量而非类型参数，导致每次执行都触发语法错误，进而进入异常分支返回false。

下面是修正后的函数，通过动态SQL实现类型兼容性检查：

create or replace function is_type(d varchar, t varchar) returns boolean as $$
begin
  -- 用动态SQL尝试将输入值转换为目标类型
  execute format('select %L::%s', d, t);
  return true;
exception
  when others then
    return false;
end;
$$ language plpgsql;

测试示例 #

• 测试超长字符串转varchar(2)：

  select is_type('cats', 'varchar(2)'); -- 返回 false
  

• 测试合法字符串转varchar(2)：

  select is_type('ca', 'varchar(2)'); -- 返回 true
  

• 测试日期格式校验：

  select is_type('2024-05-20', 'date'); -- 返回 true
  select is_type('2024/05/20', 'date'); -- 返回 false（取决于数据库日期格式设置）
  

• 测试整数校验：

  select is_type('123', 'int'); -- 返回 true
  select is_type('abc', 'int'); -- 返回 false
  

注意事项 #

• 动态SQL中使用format()函数的%L和%s占位符，避免SQL注入风险，同时正确处理输入值的转义。
• 该函数会捕获所有转换失败的异常，包括类型不兼容、长度超限、格式错误等，完全符合你“插入失败则返回false”的需求。

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