Question regarding nesting multiple functions inside of a function in Snowflake

Question

(Submitting on behalf of a Snowflake User...)

QUESTION:

Is it possible to nest multiple functions inside of a function and pass all the parameters required?

for example...

CREATE OR REPLACE FUNCTION "udf_InteractionIndicator"("ROH_RENEWAL_SYSTEM_STATUS1" VARCHAR(100), "GOLS_OPPORTUNITY_LINE_STATUS" VARCHAR(100)

                            , "ROH_CLIENT_CURRENT_TEMPERATURE1" VARCHAR(100)

                            , "ROH_PO_ATTACHED" VARCHAR(100)

                            , "ROH_PO_NUMBER" VARCHAR(100)

                            , "RT_PAID_OVERRIDE" VARCHAR(100), "ROH_RENEWAL_OPPORTUNITY_STATUS1" VARCHAR(100)

                            , "ROH_RENEWAL_CONVERSATION_DATE" DATE, "ROH_APPROVAL_RECEIVED_DATE" DATETIME)

 RETURNS NUMBER(1,0)

 AS

 $$

  CASE WHEN ("udf_RenewalNoticeSentIndicator"("ROH_RENEWAL_SYSTEM_STATUS1", "ROH_CLIENT_CURRENT_TEMPERATURE1"

                              , "GOLS_OPPORTUNITY_LINE_STATUS"

                              , "ROH_PO_ATTACHED", "RT_PAID_OVERRIDE"

                              , "ROH_RENEWAL_OPPORTUNITY_STATUS1")) = 1

      AND (ROH_RENEWAL_CONVERSATION_DATE IS NOT NULL

        OR ("udf_AuthorizedIndicator"(ROH_APPROVAL_RECEIVED_DATE, "ROH_PO_ATTACHED", "ROH_PO_NUMBER")) = 1

        OR ("udf_PaidIndicator"("GOLS_OPPORTUNITY_LINE_STATUS")) = 1

        OR ("udf_ChurnIndicator"("GOLS_OPPORTUNITY_LINE_STATUS")) = 1

        )

   THEN 1 ELSE 0 END

 $$

 ;

I've received the recommendation to:

...create a SQL UDF or JavaScript UDF. A JavaScript UDF can only contain JavaScript code, and an SQL UDF can contain only one SQL statement (no DML and DDL). In case of nesting, SQL UDF can call another SQL UDF or a JavaScript UDF but the same is not true with the JavaScript UDF(it only contains JavaScript code).

   CREATE OR REPLACE FUNCTION udf_InteractionIndicator_nested(ID DOUBLE)
     RETURNS DOUBLE
     AS
     $$
      SELECT ID
     $$;




create or replace function js_factorial(d double)
  returns double
  language javascript
  strict
  as '
  if (D <= 0) {
    return 1;
  } else {
    var result = 1;
    for (var i = 2; i <= D; i++) {
      result = result * i;
    }
    return result;
  }
  ';

CREATE OR REPLACE FUNCTION udf_InteractionIndicator(ID DOUBLE)
 RETURNS double
 AS
 $$
  select udf_InteractionIndicator_nested(ID) + js_factorial(ID)
 $$;

 select udf_InteractionIndicator(4);
+-----------------------------+
| UDF_INTERACTIONINDICATOR(4) |
|-----------------------------|
|                          28 |
+-----------------------------+

HOWEVER, I'm trying to accomplish this with a SQL UDF. It makes sense that a nested function can be created as long as they use the same parameter. I'd like to create a function that accepts say 8 parameters and the underlying functions may reference all, some or none of the parent function parameters. That is where I run into an issue... THOUGHTS?

Gavin Wilson Gavin Wilson · Accepted Answer · 2019-10-21T17:07:18

(A consultant in our community offered the following answer...)

With a JavaScript UDF the design will be much more compact and maintainable, if your use case is that there is a "main" function that breaks down work into subfunctions which will only be invoked from main.

Then you simply define all underlying functions within the main function, which is possible with JavaScript but not with an SQL UDF, and then you are free to use the main parameters everywhere.

CREATE OR REPLACE FUNCTION MAIN_JS(P1 STRING, P2 FLOAT)
RETURNS FLOAT
LANGUAGE JAVASCRIPT
AS '
    function helper_1(p) { return p * 2;  }
    function helper_2(p) { return p == "triple" ? P2 * 3 : P2; }

    return helper_1(P2) + helper_2(P1);
';

SELECT MAIN_JS('triple', 4);  -- => 20

Question regarding nesting multiple functions inside of a function in Snowflake

1 Answers