Return an array from the UDF

gzzz

Hello.

I tried to create a SQL-UDF which returns an array and got the error:

CREATE OR REPLACE FUNCTION sandbox.my_str_to_array(string VARCHAR, delimiter CHAR(1)) RETURN ARRAY[VARCHAR]
BEGIN
    RETURN STRING_TO_ARRAY(string, delimiter);
END;

vertica=> CREATE OR REPLACE FUNCTION sandbox.my_str_to_array(string VARCHAR, delimiter CHAR(1)) RETURN ARRAY[VARCHAR]
vertica-> BEGIN
vertica->     RETURN STRING_TO_ARRAY(string, delimiter);
vertica-> END;
ERROR 4856:  Syntax error at or near "ARRAY" at character 104
LINE 1: ...o_array(string VARCHAR, delimiter CHAR(1)) RETURN ARRAY[VARC...

What's wrong?
In version 11.0.2 I can't just return an array from function?!

VValdar

Hi gzzz,

It seems you're right, SQL function can't return arrays - I tried on a 12.0.4 as well, same result.
The documentation doesn't state it,
I'll open a request for specifying the datatypes accepted in the return clause.

gzzz

Thanks for checking.

Are there Vertica developers here, can they comment on this?

So strange: a native type can't be returned from a function.

gregbowers

Hello

In Vertica version 11.0.2, you can't directly specify the array type in the RETURN statement when creating a SQL UDF. Instead, you can create the function without specifying the return type and let Vertica infer it. Here's the corrected version of your code:

CREATE OR REPLACE FUNCTION sandbox.my_str_to_array(string VARCHAR, delimiter CHAR(1))
RETURN ARRAY
BEGIN
RETURN STRING_TO_ARRAY(string, delimiter);
END;

In this code, we removed the ARRAY[VARCHAR] type specification in the RETURN statement. Vertica will determine the return type based on the function's implementation.

Thank you.

gzzz

Instead, you can create the function without specifying the return type and let Vertica infer it. Here's the corrected version of your code

I tried ARRAY first, before detailed type specification and got the same error.
Now, rechecked with your code:

ERROR 4856:  Syntax error at or near "ARRAY" at character …
LINE 2: RETURN ARRAY

SruthiA

@gzzz : Arrays are not supported as return types in sql functions. if you would like to raise a feature request, please create a support case.

Sergey_Cherepan_1

Looking at all intricacies of early arrays support in v 10 and v 11... impression you will not be able to make it working. I highly doubt Vertica will be fixing array bugs back in v 10. Not sure what you are trying to achieve by pressing (free) Vertica support here.
Upgrade your server - v 12.0.4 works just fine in large prod env. Everything will be working fine after upgrade.

gzzz

Not sure what you are trying to achieve by pressing (free) Vertica support here.

I am trying to figure out array support in UDF.
There are no notes in the documentation about arrays, so maybe I'm using the wrong array syntax or format, or it's a bug and arrays can't be returned from UDFs at all.

At least Vertica can upgrade the documentation.

Upgrade your server - v 12.0.4 works just fine in large prod env. Everything will be working fine after upgrade.

Did the UDF's code from my first post work fine in v12?

VValdar said above:

It seems you're right, SQL function can't return arrays - I tried on a 12.0.4 as well, same result.

So, looks like arrays does not supported in UDF until v12.

mosheg

Just an example how you can do the same:

SELECT VERSION();
               VERSION
--------------------------------------
 Vertica Analytic Database v12.0.4-11
(1 row)

create or replace function my_str_to_array(str varchar, delimiter varchar)
RETURN VARCHAR
AS
BEGIN
RETURN '["' || REPLACE(str,delimiter,'","') || '"]';
END;
CREATE FUNCTION
SELECT my_str_to_array('abc,defg,hijk,lmnop',',') FROM DUAL;
        my_str_to_array
-------------------------------
 ["abc","defg","hijk","lmnop"]
(1 row)

SELECT STRING_TO_ARRAY(my_str_to_array('abc,defg,hijk,lmnop',',') USING PARAMETERS collection_delimiter=',') FROM DUAL;
        STRING_TO_ARRAY
-------------------------------
 ["abc","defg","hijk","lmnop"]
(1 row)

SELECT STRING_TO_ARRAY(my_str_to_array('abc,defg,hijk,lmnop',',') USING PARAMETERS collection_delimiter=',')::ARRAY[VARCHAR] FROM DUAL;
        STRING_TO_ARRAY
-------------------------------
 ["abc","defg","hijk","lmnop"]
(1 row)

gzzz

Of course I can return from UDF any string, including looks like array.
But it's not an array, it's a string.

Instead use UDF returning string-array and STRING_TO_ARRAY easier just use STRING_TO_ARRAY(str, ',') or STRING_TO_ARRAY(str USING PARAMETERS collection_delimiter = ',') in modern syntax.
Of course, my example is only primitive example of real use case.
It simply demonstrates the impossibility of returning an array from a UDF.

My question is about returning an array and why Vertica still can't do this at version 12 and there's nothing in documentation about this kind of limitation.

mosheg

Thank you gzzz,
VValdar Opened already a request for specifying the datatypes accepted in the return clause.

gzzz

VValdar Opened already a request for specifying the datatypes accepted in the return clause.

OK, thanks.
I hope that in the future all supported types will be available in UDFs, and not just the documentation will be fixed.