6
0
User-defined functions (UDFs) let you extend the system to perform operations that are not available through the built-in, system-defined functions provided by Snowflake. Snowflake UDF currently supports two types: SQL and JavaScript:
- A SQL UDF evaluates an arbitrary SQL expression and returns either scalar or tabular results.
- A JavaScript UDF lets you use the JavaScript programming language to manipulate data and return either scalar or tabular results.
SCALAR UDF:
Scalar UDF : By default, a UDF is scalar, returning at most one row, consisting of a single column/value.
Say you have Invoice table and getting INVC_CODE data in the form of 2 digit char value, For Business these codes are not much relevance and they want the proper definition of these codes and also don’t want to do this conversion again and again. So we have created a Snowflake UDF which will do the required transformation and can be used on demand.
Also we have take care to avoid the error of Unsupported type as Snowflake does not support correlated subquery in the select clause and reports unsupported subquery error.
So we have used ANY clause inside the UDF.
create or replace table Invoice
(inv_no varchar(10), Inv_Amt number(10), Inv_date date,
Invc_code varchar(2));
insert into Invoice (inv_no,inv_Amt,inv_date,invc_code)
values
(‘INV_100′,100,’2020-11-01′,’SP’),
(‘INV_101′,200,’2020-11-01′,’PP’),
(‘INV_102′,300,’2020-11-01′,’IP’);
create or replace function INVC_CODE_EVALUATE (inv varchar)
returns VARCHAR
as $$select
any_value(case
when invc_code = ‘SP’ then ‘SettlePayment’
when invc_code = ‘PP’ then ‘PartialPayment’
else ‘unknown’
end )as invc_des
from invoice $$
select A.*,INVC_CODE_EVALUATE (a.inv_no) from invoice a where a.inv_no = ‘INV_100’
TABULAR UDF:
However, a UDF can also be defined to return a set of rows with one or more columns, which can then be accessed in the FROM clause of a query. A user-defined table function (UDTF) is defined by specifying a return type that contains the TABLE keyword and specifies the names and types of the columns in the table results
Say we have two tables Customers and Orders and we need to identify the Customer details along with the Order information, then a join needs to be done among these tables. Being one of the highly used these tables inside the warehouse and Customer and Order information is required for multiple purpose in multiple locations inside the code. So writing the join each time for every operation is cumbersome,so we can create a tabular UDF which return the Data with multiple columns.
CREATE TABLE customers
(
customernumber varchar(100) PRIMARY KEY,
customername varchar(50),
phonenumber varchar(50),
postalcode varchar(50),
locale varchar(10),
datecreated date,
email varchar(50)
);
CREATE TABLE orders
(
customernumber varchar(100) ,
ordernumber varchar(100) PRIMARY KEY,
comments varchar(200),
orderdate date,
ordertype varchar(10),
shipdate date,
discount float,
quantity int,
productnumber varchar(50)
);
create or replace function getcustomer()
returns table (customername varchar, email varchar,customernumber varchar, ordernumber varchar)
as 'select c.customername, c.email,c.customernumber,o.ordernumber from customers c,orders o
where c.customernumber = o.customernumber';
or
create or replace function getcustomer1(customernumber number)
returns table (customername varchar, email varchar,customernumber number, ordernumber varchar)
as 'select customername, email,customernumber,o.ordernumber from customers c,orders o
where c.customernumber = o.customernumber and
c.customernumber = customernumber';
Call UDF:
select * from table(getcustomer ()) c where c.customernumber = 948;
select * from table(getcustomer1 (948)) c
