CODEWITHSUNDEEP
pythonjsonamazon-web-servicesamazon-redshiftuser-defined-functions
ScottieB
ScottieB

Reputation: 4052

Can you return multiple values from a python UDF in Redshift?

We use JSONB to store elements occasionally. This gets passed to Redshift as a string, that I then parse using a UDF. For an audit report, I want to present one part of the JSON in one column, and a different part in the other. Rather than call two UDFs in every row, can I call one UDF that returns two values?

As a toy example consider a transactional database where for every row we store the method a customer used to pay. Some customers can pay with multiple amounts (eg spend down a gift card and then cover the difference in cash), and thus we store a JSON blob in this field.

{"Methods": [
   {"Type":"Gift Card", "Amount": 5.74}
    ,{"Type":"Cash", "Amount": 4.26}
],
"Coupons": [
   {"Code": "XHAY12", "Amount":1.22},
   {"Code": "Y123A", "Amount": 4.66}
]}

In my report, I want a column (methods) that shows the cash amount and a second column coupons > $5) that shows whether the transaction got more than $5 off.

I've tried RETURNS float, int as well with brackets, braces and parentheses but get generic syntax errors. Anyone have a suggestion? Or do I need to suck it up and have separate functions?

Upvotes: 2

Views: 2269

Answers (2)

AlexYes
AlexYes

Reputation: 4208

As a workaround, you can create a UDF that returns JSON with 2 key/value pairs, run it in a subquery and then parse the result with native Redshift function that gets the specific key like this:

WITH
udf_subquery as (
    SELECT
     id
    ,your_udf(parameters) as your_udf_json_result
    FROM your_table
)
SELECT
 id
,json_extract_path_text(your_udf_json_result,'key1') as col1
,json_extract_path_text(your_udf_json_result,'key2') as col2
FROM udf_subquery;

Upvotes: 6

John Rotenstein
John Rotenstein

Reputation: 270154

No. The scalar UDF returns only one value.

You could pass multiple values via a string, but there is no concept of storing values in a variable for further manipulation (eg splitting out values).

From Creating a Scalar Python UDF:

A scalar Python UDF incorporates a Python program that executes when the function is called and returns a single value.

Upvotes: 2

Related Questions