title: Generate a date range in Snowflake within a tabular UDF
body: <p>I'm trying to replace a PostGres function that generates a list of timestamps using the current timestamp that starts [X] [start datepart]s ago and ends with the current timestamp with an interval of [end datepart]s.  For example, a user could ask for a series that starts five days ago and returns a timestamp for every hour.</p>
  <p>I've found plenty of examples that do something like this using the GENERATOR function. This is how it is done now:</p>
  <pre><code>CREATE OR REPLACE FUNCTION fn_get_timestamps_in_range(grain VARCHAR, start_tsmp TIMESTAMP_TZ, maxrange NUMERIC(38,0)) 
  RETURNS TABLE(out_tsmp TIMESTAMP_TZ)
  LANGUAGE SQL 
  STRICT 
  AS 
  $$
  SELECT CONVERT_TIMEZONE('UTC', CASE LOWER(grain) 
                                      WHEN 'min'   THEN DATEADD('min',  ROW_NUMBER() OVER(ORDER BY NULL) - 1, start_tsmp)
                                      WHEN 'hour'  THEN DATEADD('hour', ROW_NUMBER() OVER(ORDER BY NULL) - 1, start_tsmp)
                                      WHEN 'day'   THEN DATEADD('day',  ROW_NUMBER() OVER(ORDER BY NULL) - 1, start_tsmp)
                                      WHEN 'month' THEN DATEADD('month',ROW_NUMBER() OVER(ORDER BY NULL) - 1, start_tsmp)
                                      WHEN 'year'  THEN DATEADD('year', ROW_NUMBER() OVER(ORDER BY NULL) - 1, start_tsmp)
                                 END) FROM TABLE(GENERATOR(ROWCOUNT=&gt;maxrange)) ORDER BY 1
  $$
  ;
  </code></pre>
  <p>However, to use that function I have to provide maxrange -- i.e. know how many rows are going to be returned -- so I have to do a DATEDIFF([datepart], start, end).  I have this in a function:</p>
  <pre><code>    CREATE OR REPLACE FUNCTION fn_get_datediff(grain VARCHAR, start_tsmp TIMESTAMP_TZ, end_tsmp TIMESTAMP_TZ) 
  
  RETURNS NUMBER(18,0)
  LANGUAGE SQL 
  STRICT 
  AS 
  $$
  CASE LOWER(grain) 
       WHEN 'min'   THEN DATEDIFF('min',  start_tsmp, end_tsmp)
       WHEN 'hour'  THEN DATEDIFF('hour', start_tsmp, end_tsmp)
       WHEN 'day'   THEN DATEDIFF('day',  start_tsmp, end_tsmp)
       WHEN 'month' THEN DATEDIFF('month',start_tsmp, end_tsmp)
       WHEN 'year'  THEN DATEDIFF('year', start_tsmp, end_tsmp)
       ELSE -1
  END + 1::NUMERIC(18,0)
  $$
  ;
  </code></pre>
  <p>Try as I may, the GENERATOR will not accept that result -- I keep getting &quot;argument 1 to function GENERATOR needs to be constant&quot; error.  Even if the calc is performed in a calling UDF, the error still returns.</p>
  <p>For example:</p>
  <pre><code>SELECT * FROM TABLE(fn_get_timestamps_in_range(
                          $end_grain  
                         ,fn_get_offset_start_tsmp($start_grain, 1 - $start_range, $time_zone)
                         ,fn_get_datediff($end_grain
                                             ,etl.fn_get_offset_start_tsmp($start_grain, 1 - $start_range, $time_zone)
                                             ,etl.fn_normalize_date($end_grain, CURRENT_TIMESTAMP, $time_zone))
                      )
  )
  ;
  </code></pre>
  <p>...returns the error:</p>
  <pre><code>SQL compilation error: argument 1 to function GENERATOR needs to be constant, found '(CAST(DATE_DIFFTIMESTAMPINMINUTES(DATE_ADDMINUTESTOTIMESTAMP(-19, ENSURE_NULLABLE('2022-01-05 21:01:00.000000000Ztz=1080')), ENSURE_NULLABLE('2022-01-05 21:01:00.000000000Ztz=1080')) AS NUMBER(18,0))) + 1'
  </code></pre>
  <p>If I replace the datediff call with a number, it works fine.</p>
  <p>How can I get the GENERATOR function to accept the values I need?</p>