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$%'
    $$;