Vertica创建RAW.getType自定义函数报错，寻求正确实现方案

错误原因分析 #

1. 第一个函数报错Column "input_string" does not exist：

   • Vertica原生SQL函数不支持BEGIN/END的PL/SQL风格结构，参数在这种写法的子查询中无法被正确识别
   • 表中LENGTH列与内置函数LENGTH()重名，导致数据库混淆两者含义

2. 第二个函数的语法错误：

   • PL/pgSQL语法中，DECLARE语句必须放在BEGIN块之前，不能嵌套在BEGIN内部
   • 同样存在LENGTH列名与内置函数重名的问题

正确写法 #

写法一：Vertica原生SQL函数（推荐，无需额外启用语言） #

CREATE OR REPLACE FUNCTION RAW.getType(input_string VARCHAR)
RETURNS VARCHAR
AS $$
    SELECT Type
    FROM RAW.TYPE
    WHERE LENGTH(input_string) = "LENGTH"  
      AND input_string LIKE PATTERN || '%' 
    LIMIT 1;
$$ LANGUAGE SQL;

• 用RETURNS替代RETURN（Vertica函数定义标准语法）
• 用$$包裹函数体，指定LANGUAGE SQL
• 表中LENGTH列用双引号包裹，明确区分于内置LENGTH()函数

写法二：PL/pgSQL风格函数（需确保Vertica启用PL/pgSQL支持） #

CREATE OR REPLACE FUNCTION RAW.getType(num VARCHAR)
RETURNS VARCHAR
AS $$
DECLARE
    result VARCHAR;
BEGIN
    SELECT Type INTO result
    FROM RAW.TYPE
    WHERE LENGTH(num) = "LENGTH"  
      AND num LIKE PATTERN || '%'  
    LIMIT 1;
    RETURN result;
END;
$$ LANGUAGE plpgsql;

• 调整DECLARE语句到BEGIN块之前，符合PL/pgSQL语法规范
• 同样用双引号包裹LENGTH列名避免混淆

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