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

Question

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?

AlexYes · Accepted Answer · 2017-09-06 09:20:03Z

6

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;

answered Sep 6, 2017 at 9:20

AlexYes

4,2282 gold badges18 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

John Rotenstein · Accepted Answer · 2017-09-06 02:27:12Z

2

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.

answered Sep 6, 2017 at 2:27

John Rotenstein

273k28 gold badges456 silver badges541 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related