Return an array from the UDF

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.

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.

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.

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

@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.

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.

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.

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)

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.

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

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.

Is it possible to use array values as input and/or output in Vertica 25?
https://www.vertica.com/docs/9.2.x/HTML/CppSDK/class_vertica_1_1_column_types.htm

Have a look at the matrix_multiply example under /opt/vertica/sdk/examples/python/ScalarFunctions.py
This is a Python example, but Python SDK notionally wraps the C++ one.