Administrator

How to use check inbuilt user defined functions in Snowflake

How to use function overloading 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
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
use role sysadmin;
use database ttips;
use schema udfs;

-- 
show functions;


-- how many javascript or sql
select  "language", count(1) from table(result_scan('01a4d8ed-0000-9eda-0002-5532000bb946')) t 
where "is_builtin" = 'Y'
group by "language";

--how many ansi compatible functions
select  "is_ansi", count(1) from table(result_scan('01a4d8ed-0000-9eda-0002-5532000bb946')) t 
where "is_builtin" = 'Y'
group by "is_ansi";


-- how many aggregated
select  "is_aggregate", count(1) from table(result_scan('01a4d8ed-0000-9eda-0002-5532000bb946')) t 
where "is_builtin" = 'Y'
group by "is_aggregate";

-- how many system functions
select  "name","is_aggregate" , "is_ansi","is_table_function","valid_for_clustering",
"arguments", "description"
from table(result_scan('01a4d8ed-0000-9eda-0002-5532000bb946')) t 
where "is_builtin" = 'Y' and "name" like 'SYSTEM$%'
order by "is_ansi";
-- there are total 40 system functions.


-- valid for clustering
-- how many system functions
select  "name","is_aggregate" , "is_ansi","is_table_function","valid_for_clustering",
"arguments", "description"
from table(result_scan('01a4d8ed-0000-9eda-0002-5532000bb946')) t 
where "is_builtin" = 'Y' and "valid_for_clustering" = 'Y';
-- if this flag is true.. 
-- True if the UDF can be used in a CLUSTER BY expression

-- if you have not see my very important video on cluster .. request you to watch
-- https://youtu.be/UNBysn1M9Vg (Ch-13 in this playlist)



show functions like 'UPPER';
-- it is builtin, takes one arg and return float
-- there is two functions with same name
-- it is aggregated function and it is ansi standard also

-- lets try to desc it
desc function UPPER(VARCHAR);
-- you can't desc the system function

-- can I see the ddl
select get_ddl('function','UPPER(VARCHAR)');

-- Can I creat a function with same name
create or replace function UPPER(txt VARCHAR)
returns string 
language sql
comment = 'trying to create a system function'
as
$$
    select 'upper-txt'
$$;

show functions like 'UPPER';


select *  from table(information_schema.task_history()) where name ='TASK_L1_A' order by scheduled_time;
        

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