CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platformuser-defined-functions
The_Third_Eye
The_Third_Eye

Reputation: 183

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

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?

Upvotes: 1

Views: 252

Answers (2)

Felipe Hoffa
Felipe Hoffa

Reputation: 59175

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

enter image description here

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'
$$
;

Upvotes: 1

Gokhan Atil
Gokhan Atil

Reputation: 10089

The parameter you defined in SQL UDF will be evaluated as a literal:

When you call the function like PRODUCT_OF_COL_VAL('Col'), the SQL statement you execute becomes:

SELECT EXP(SUM(LN('Col'))) AS RESULT from SCHEMA.SAMPLE_TABLE

What you want to do is to generate a new SQL based on parameters, and it's only possible using "stored procedures". Check this one:

Dynamic SQL in a Snowflake SQL Stored Procedure

Upvotes: 0

Related Questions