Snowflake - How to call a UDF within a Procedure

Question

In the following, TEST_DATA is my data and TEST_VALIDATE is the table I am validating against. I want to test TEST_DATA.DVALUE against TEST_VALIDATE and adjust the DVALUE such that it is always within a given range. The UDF TEST_RANGE() does this and returns the adjusted buffered value:

CREATE TABLE TEST_DATA
(
  MNEMONIC      VARCHAR2(3),
  DVALUE        NUMBER(5,1)
);

INSERT INTO TEST_DATA ( MNEMONIC, DVALUE ) VALUES ( 'AT', 2 );
INSERT INTO TEST_DATA ( MNEMONIC, DVALUE ) VALUES ( 'BP', 88 );

CREATE TABLE TEST_VALIDATE
(
  MNEMONIC          VARCHAR2(3),
  MINIMUM_VALUE     NUMBER(5,1),
  MAXIMUM_VALUE     NUMBER(5,1)
);

INSERT INTO TEST_VALIDATE ( MNEMONIC, MINIMUM_VALUE, MAXIMUM_VALUE ) VALUES ( 'AT', 5.1, 10.1 );
INSERT INTO TEST_VALIDATE ( MNEMONIC, MINIMUM_VALUE, MAXIMUM_VALUE ) VALUES ( 'BP', 2.2, 100.2 );


CREATE OR REPLACE FUNCTION TEST_RANGE( p_mnemonic VARCHAR2, p_reading NUMBER(5,1) )
RETURNS NUMBER AS
'SELECT
MAX(CASE
    WHEN p_reading BETWEEN MINIMUM_VALUE AND MAXIMUM_VALUE THEN p_reading
    WHEN p_reading < MINIMUM_VALUE THEN MINIMUM_VALUE
    WHEN p_reading > MAXIMUM_VALUE THEN MAXIMUM_VALUE
    ELSE NULL
END)
FROM TEST_VALIDATE
WHERE MNEMONIC = p_mnemonic';

This test executes fine:

SELECT TEST_RANGE( 'AT', 55 );

but this attracts the following error "002031 (42601): SQL compilation error: Unsupported subquery type cannot be evaluated" -- WHY:

SELECT TEST_RANGE( MNEMONIC, DVALUE ) FROM TEST_DATA;

So I thought of creating a Stored Procedure to do this, but how do you call a UDF in a Procedure? I thought this was pretty basic but I can't find any post on it. Do I have to wrap the UDF in a createStatement().execute().getColumnValue() routine? Is there a more readable way than this?

CREATE OR REPLACE PROCEDURE EDW_WEATHER.TEST_SP ()
 RETURNS VARIANT
 LANGUAGE JAVASCRIPT
AS $$
  var rs = snowflake.createStatement( { sqlText: `SELECT MNEMONIC, DVALUE FROM TEST_DATA`, binds:[] } ).execute();

  var results_array = [];
  while (rs.next()) {
    // This is wrong. How do I call the UDF???
    results_array.push( rs.select( TEST_RANGE( rs.getColumnValue('MNEMONIC'), rs.getColumnValue('DVALUE') ) ) );
  }
  return results_array;
$$

Answer 1

To answer your last question how you call a UDF within a Stored Procedure: You can trigger the UDF the same way you do outside the SP.

You have to use the .execute()-method, trigger a SELECT myUDF() with it and then retrieve the resultset for further consumption & processing.