Including Suggested Parameter Values in UDFs

When your partners run a user-defined function (UDF) that you created for them, they don't always know what values would be valid, so it can be a challenge to get the UDF to run without errors. If you specify suggested parameter values when you create your UDFs, it makes it easier for your partners to set values if they can see a set of possible valid parameters.

You can include suggestions by using the following comment syntax so that the comments will be passed to the user:

-- @hint {"id_parameter_name": {"one_of": [string1, string2, string3]}, 
--        "date_range_parameter_name1": {"range": ['date1', 'date2'] },
--        "date_range_parameter_name2": {"range": ['date1', 'date2'] },
--        "range_parameter_name": {"range": [integer1, integer2] },
--        "multi-select_parameter_name": {"any_of": ['string1', 'string2', 'string3'] }
--        }
create or replace function udf_name(
  id_parameter_name string, 
  date_range_parameter_name1 date, 
  date_range_parameter_name2 date,
  range_parameter_name integer,
  multi-select_parameter_name array<integer>
)

For example:

-- @hint {"input_campaign_id": {"one_of": [12345, 56789, 15325]}, 
--        "campaign_start_date": {"range": ['2023-01-01', '2023-05-01'] },
--        "campaign_end_date": {"range": ['2023-02-01', '2023-06-01'] },
--        "conversion_windows_days": {"range", [1, 14] },
--        "device_types": {"any_of": ['mobile', 'web', 'tv'] }
--        }
create or replace function attributed_conversions_by_campaign(
  input_campaign_id string, 
  campaign_start_date date, 
  campaign_end_date date,
  conversion_windows_days integer,
  device_types array<integer>
)

Once you've created a UDF that includes suggested parameter commenting, you can view it in SQL Editor by selecting Open Definition from the UDF's More Options menu ().