how to create a snowflake udf that makes use of time_slice to return a table-CodePudding

Is there a way to create a snowflake udf or stored procedure that returns a table grouped by time_slice where the week starts on a day other than Monday? The SQL is simple, however to use time_slice by week with a start day other than Monday, one must first call ALTER SESSION SET WEEK_START. I can put the ALTER SESSION call within a stored procedure, and put the time_slice sql within a user defined function to return a table, but I haven't been able to make all calls within a SINGLE function or stored procedure. Here's sample sql.

ALTER SESSION SET WEEK_START = 3;
select
       time_slice(s_startdateutc, 1, 'WEEK', 'START') as BLOCKSTART,
       time_slice(s_startdateutc, 1, 'WEEK', 'END')   as BLOCKEND,
       count(*) as ACTIONCOUNT
    from mytable
    where s_startdateutc between $REPORTSTARTDATE and $REPORTENDDATE
    group by BLOCKSTART, BLOCKEND;

The following code works, but if you uncomment out the ALTER SESSION line, it fails.

create or replace function nr_sessacts_application_overview(reportStart date, reportEnd date)
    returns table (BLOCKSTART date, BLOCKEND date, ACTIONCOUNT number)
    language sql
    as
    $$
    --ALTER SESSION SET WEEK_START = 3;    
    select
       to_date(time_slice(s_startdateutc, 1, 'WEEK', 'START')) as BLOCKSTART,
       to_date(time_slice(s_startdateutc, 1, 'WEEK', 'END'))   as BLOCKEND,
       count(*) as ACTIONCOUNT
    from mytable
    where s_startdateutc between reportStart and reportEnd
    
    group by BLOCKSTART, BLOCKEND
    order by BLOCKSTART
    $$
    ;

CodePudding user response：

What if you looked for the nearest Tuesday in the block?

select next_day(s_startdateutc::date-7, 'Tue') as blockstart,
       next_day(s_startdateutc::date, 'Tue')   as blockend,
       count(*) as actioncount
from mytable
where s_startdateutc between $REPORTSTARTDATE and $REPORTENDDATE
group by blockstart, blockend;

CodePudding user response：

Solved. The key is to include EXECUTE AS CALLER.

create or replace function nr_sessacts_application_overview(reportStart date, reportEnd date)
    returns table (BLOCKSTART date, BLOCKEND date, ACTIONCOUNT number)
    language sql
    EXECUTE AS CALLER
    as
    $$
    ALTER SESSION SET WEEK_START = 3;    
    select
       to_date(time_slice(s_startdateutc, 1, 'WEEK', 'START')) as BLOCKSTART,
       to_date(time_slice(s_startdateutc, 1, 'WEEK', 'END'))   as BLOCKEND,
       count(*) as ACTIONCOUNT
    from mytable
    where s_startdateutc between reportStart and reportEnd
    
    group by BLOCKSTART, BLOCKEND
    order by BLOCKSTART
    $$
    ;