Vertica:创建递归函数

Question

我正在努力解决Vertica的HEX_to_BINARY函数只支持VARCHAR而不支持LONG VARCHAR的问题。

我正在尝试创建一个递归SQL函数来解决这个问题。

CREATE OR REPLACE FUNCTION LONG_HEX_TO_BINARY(HEX_VALUE LONG VARCHAR) RETURN LONG VARBINARY
AS
BEGIN
RETURN (CASE WHEN (LENGTH(HEX_VALUE) > 65000) THEN (HEX_TO_BINARY(CAST(SUBSTR(HEX_VALUE,1,65000) as VARCHAR(65000))) || LONG_HEX_TO_BINARY(SUBSTR(HEX_VALUE,65001))) ELSE HEX_TO_BINARY(CAST(HEX_VALUE AS VARCHAR(65000)))  END);
END;

然而，当我尝试编译它(使用psql(时，我得到了

ERROR:  Syntax error at or near "EOL"

使用Vertica的vsql客户端我得到

ROLLBACK 3457: Function LONG_HEX_TO_BINARY(long varchar) does not exist, or permission is denied for LONG_HEX_TO_BINARY(long varchar) HINT: No function matches the given name and argument types. You may need to add explicit type casts

有什么想法吗，创建递归函数是允许的吗？

Answer 1

这似乎适用于

首先创建一个"伪"函数来建立签名

dbadmin=> CREATE OR REPLACE FUNCTION LONG_HEX_TO_BINARY(HEX_VALUE LONG VARCHAR)
dbadmin-> RETURN LONG VARBINARY
dbadmin-> AS
dbadmin-> begin
dbadmin->    return NULL;
dbadmin-> end;
CREATE FUNCTION
dbadmin=> /

现在用真正的功能代替它

dbadmin=> CREATE OR REPLACE FUNCTION LONG_HEX_TO_BINARY(HEX_VALUE LONG VARCHAR)
dbadmin-> RETURN LONG VARBINARY
dbadmin-> AS
dbadmin-> BEGIN
dbadmin->    RETURN (
dbadmin(>      CASE
dbadmin(>        WHEN (LENGTH(HEX_VALUE) > 65000) THEN
dbadmin(>      (HEX_TO_BINARY(CAST(SUBSTR(HEX_VALUE,1,65000) as VARCHAR(65000))) || LONG_HEX_TO_BINARY(SUBSTR(HEX_VALUE,65001)))
dbadmin(>        ELSE
dbadmin(>  HEX_TO_BINARY(CAST(HEX_VALUE AS VARCHAR(65000)))
dbadmin(>   END
dbadmin(> );
dbadmin-> END;
CREATE FUNCTION

现在看看它是否真的有效。。