Administrator

How to use strict property in Snowflake UDFs

How to use strict property in Snowflake UDFs

User Defined Functions in Snowflake Video

You can watch the complete hands on video tutorial

Step-5: SQL Construct

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
use role sysadmin;
use database ttips;
use schema udfs;

-- we have already seen how to create scalar function.. 
-- now lets understand how to create a simple tabular function


create or replace function my_tabular_udf()
    returns table(name varchar,is_aggregate varchar, is_ansi varchar, arguments varchar,description  varchar)
    as
    $$
        select  "name","is_aggregate" , "is_ansi","arguments", "description"
        from table(result_scan('01a4d8ed-0000-9eda-0002-5532000bb946')) t 
        where "is_builtin" = 'Y' and "name" like 'SYSTEM$%'
    $$;
    
select * from table(my_tabular_udf());

desc function my_tabular_udf();

show functions like '%MY_T%';

-- so you can build a complex User Defined functions using tabular functions.
-- all other parameters, we have seen for scalar function like secure, not null etc 
-- are applicable for tabular function too.

-- we are going to cover advance concept of this topic in a separte video
-- for this video, we will keep the scope limited.


-- couple of specifiction works differently in user defined table function
create or replace function table_udf_not_null()
    returns table(name varchar,is_aggregate varchar, is_ansi varchar, arguments varchar,description  varchar) not null
    as
    $$
        select  "name","is_aggregate" , "is_ansi","arguments", "description"
        from table(result_scan('01a4bdf3-0000-9c25-0002-5532000843ea')) t 
        where "is_builtin" = 'Y' and "name" like 'SYSTEM$%'
    $$;
    

        

All SQL Scripts - Part 01 to Part 09

  1. Part-02 User Defined Function in Snowflake SQLs
  2. Part-02 JavaScript & SQL User Defined Function in Snowflake SQLs
  3. Part-03 Strict Property User Defined Function in Snowflake SQLs
  4. Part-04 Not Null Return User Defined Function in Snowflake SQLs
  5. Part-05 Secure User Defined Function in Snowflake SQLs
  6. Part-06 Inbuilt Vs. User Defined Function in Snowflake SQLs
  7. Part-07 Table User Defined Function in Snowflake SQLs
  8. Part-08 Method Overloading SQL Script
  9. Part-09 Java User Defined Function SQL & Java Code