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;