'How to use SELECT/INSERT statement in snowflake UDF
I have a requirement where I need to create a UDF in snowflake but it has complex Select and Insert statements. But as per the documentation, SELECT statement cannot be used in UDF. Is there any possible workaround?
Solution 1:[1]
You can actually do that in UDF . For eg: create or replace function orders_for_product(prod_id varchar) returns table (product_id varchar, quantity_sold numeric(11, 2)) as $$ select product_id, quantity_sold from orders where product_id = prod_id $$ ;
Solution 2:[2]
User-defined functions accept parameters, perform an operation(some calculation) and return the result of that operation as a value. The operations that are not available through the built-in, system-defined functions are generally defined using UDFs.
However, you can incorporate SELECT in the UDF as provided below and some examples of how a UDF can be used are provided in the link shared.
CREATE FUNCTION profit()
RETURNS NUMERIC(11, 2)
AS
$$
SELECT SUM((retail_price - wholesale_price) * number_sold) FROM purchases
$$
;
https://docs.snowflake.com/en/sql-reference/udf-overview.html#sql
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Manisha J |
| Solution 2 | sprethepa |