PostgreSQL函数输入输出类型无关实现？适配FROM_UNIXTIME遇阻

问题分析 #

当前实现的from_unixtime函数固定返回timestamp类型，但MySQL的FROM_UNIXTIME会根据传入的格式参数返回不同格式的字符串（比如完整时间戳、日期、时分秒等）。当返回如'23:31:30'这类仅含时间的字符串时，PostgreSQL会尝试将其强制转换为timestamp，但该字符串无法解析为合法的时间戳类型，因此抛出转换错误。

解决方案 #

将函数返回类型改为text，统一返回格式化后的字符串，完全对齐MySQL的FROM_UNIXTIME行为，无需修改项目中的原有SQL语句。

修改后的函数代码：

CREATE OR REPLACE FUNCTION "public"."from_unixtime"("indate" anyelement, "informate" text)
  RETURNS text AS $BODY$
BEGIN
    -- 完整时间格式
    IF upper($2) = upper('%Y-%m-%d %H:%i:%s') THEN
        return to_char(to_timestamp($1), 'YYYY-MM-DD HH24:MI:SS');
    END IF;

    -- 日期格式
    IF upper($2) = upper('%Y-%m-%d') THEN
        return to_char(to_timestamp($1), 'YYYY-MM-DD');
    END IF;
        
    -- 时分格式
    IF upper($2) = upper('%H:%i') THEN
        return to_char(to_timestamp($1), 'HH24:MI');
    END IF;
        
    -- 时分秒格式
    IF upper($2) = upper('%H:%i:%s') THEN
        return to_char(to_timestamp($1), 'HH24:MI:SS');
    END IF;

    -- 其他格式可继续添加分支
    raise exception 'undefined format: %', $2;
    return null;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

关键修改点说明 #

1. 返回类型调整：将返回类型从timestamp改为text，彻底避免类型转换冲突。
2. 统一格式化逻辑：所有分支都通过to_char直接生成对应格式的字符串，去掉原有的多余转换步骤，简化逻辑的同时完全对齐MySQL的输出行为。
3. 保留格式兼容性：保留原有的大小写不敏感格式匹配逻辑，确保兼容项目中已有的SQL写法。

测试验证 #

• 完整时间格式查询：

  SELECT from_unixtime(1234567890, '%Y-%m-%d %H:%i:%s'), pg_typeof(from_unixtime(1234567890, '%Y-%m-%d %H:%i:%s'));
  

  返回结果：2009-02-13 23:31:30 | text，符合预期。

• 时分秒格式查询：

  SELECT from_unixtime(1234567890, '%H:%i:%s'), pg_typeof(from_unixtime(1234567890, '%H:%i:%s'));
  

  返回结果：23:31:30 | text，无报错。

• 日期格式查询：

  SELECT from_unixtime(1234567890, '%Y-%m-%d'), pg_typeof(from_unixtime(1234567890, '%Y-%m-%d'));
  

  返回结果：2009-02-13 | text，正常运行。

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