Issue While Creating Product of All Values Of Column (UDF in Snowflake)

Question

I was trying to create a Snowflake SQL UDF Where it computes the Values of the all values and will return the result to the user.

So firstly, i have tried the following approach

# The UDF that Returns the Result.

CREATE OR REPLACE FUNCTION PRODUCT_OF_COL_VAL()
RETURNS FLOAT
LANGUAGE SQL
AS
$$
    SELECT EXP(SUM(LN(COL))) AS RESULT FROM SCHEMA.SAMPLE_TABLE
$$

The above code executes perfectly fine.... if you could see above (i have hardcoded the TABLE_NAME and COLUMN_VALUE) which is not i acutally want..

So, i have tried the following approach, by passing the column name dynamically..

create or replace function (COL VARCHAR)
RETURNS FLOAT
LANGUAGE SQL
AS
$$
SELECT EXP(SUM(LN(COL))) AS RESULT from SCHEMA.SAMPLE_TABLE
$$

But it throws the following issue...

Numeric Value 'Col' is not recognized

To elaborate more the Data type of the Column that i am passing is NUMBER(38,6) and in the background its doing the following work..

EXP(SUM(LN(TO_DOUBLE(COL))))

Why is this running fine in Scenario 1 and not in Scenario 2?

Answer 1

Hopefully we will be able to have this kind of UDFs one day, in the meantime consider this answer using ARRAY_AGG() and a Python UDF:

Sample usage:

select count(*) how_many, multimy(array_agg(score)) multiplied, tags[0] tag
from stack_questions
where score > 0 
group by tag
limit 100

The UDF in Python - which also protects against numbers beyond float's limits:

create or replace function multimy (x array)
returns float
language python
handler = 'x'
runtime_version = '3.8'
as
$$
import math
def x(x):
    res = math.prod(x)
    return res if math.log10(res)<308 else 'NaN'
$$
;