Skip to main content

Using Tables and Views as UDF Parameters

You can create a user-defined function (UDF) that specifies a table or a view as a parameter.

When you create a UDF, you can optionally specify access to the following:

  • A combination of table or view and specified data types (integer, string, or date)

  • One or more columns, with no access to columns that are not specified

You or your partner can then run the UDF and pass a table or view as a parameter. The table or view used as a parameter must have the exact column names, column data types, and column order as specified when the UDF was created.

Specifying a Single Column

Use the following syntax to create a UDF that specifies a table or a view with a single column:

Syntax:

create or replace function <UDF_name> ( [ <parameter> ] ) as <sql_expression>
Example 1. Example UDF Specifying a Single Column

The following UDF creation statement specifies a table or a view with a single column:

create or replace function simple_count2(mydata< rampid: string>) as select count(*) from mydata

Where:

  • simple_count2: The name of the UDF

  • mydata: The name of the parameter

  • rampid: The name of the column

  • string: The data type of the rampid column

The syntax of a simple parameter is contained within the parentheses after the UDF and has the following pattern:

parameter_name data< column_name: column_data_type>

To run the UDF, pass the name of a table or view or pass a query as shown in the following examples:

  • Pass a view or table whose columns exactly match those in the UDF creation statement.

    select * from simple_count2(brands_rampid)

    Where: brands_rampid is a view that was created ahead of time to contain only the rampid column.

  • Pass a SQL query enclosed in parentheses that computes to the exact column specified in the UDF creation statement.

    select * from simple_count2((select rampid from brands))


Specifying Multiple Columns

You can create UDFs that specify multiple comma-delimited column names to be run against a table or a view that has the same column names. If your table or view has any columns other than those specified in the creation statement, you will receive an error.

Example 2. Example UDF Specifying Multiple Columns

The following UDF creation statement example specifies a table or a view with multiple columns:

create or replace function webvisitors(customers data< age_range: string,web_visit_datetime: timestamp,rampid: string,online_transaction: int>) as select count(*) from customers

Where:

  • webvisitors: The name of the UDF

  • customers: The name of the parameter

  • age_range: string,web_visit_datetime: timestamp,rampid: string,online_transaction: int: Specifies multiple comma-delimited column names and their corresponding column data types

To run the UDF, pass the name of a table or view or pass a query as shown in the following examples:

  • Pass a view or table whose columns match those in the UDF creation statement.

    select * from webvisitors(brands)

  • Run a SQL query enclosed in parentheses against the exact columns specified in the UDF creation statement.

    select * from webvisitors((select age_range,web_visit_datetime,rampid,online_transaction from customers))

Where: brands is a table that contains the columns age_range, web_visit_datetime, rampid, and online_transaction, which were in the UDF creation statement



Specifying a Combination of Parameter Types

You can create UDFs that include a combination of parameter types.

Example 3. Example UDF Specifying Multiple Parameters

UDFs can contain multiple parameters, which can be any combination of table or view, integer, string, or date and time as shown in the following example UDF creation statement.

create or replace function campaign_overlap(campaign int, channel string, inputdatetime date, customers data< rampid: string>) as select property, count(distinct c.rampid) as overlap from pub2 join customers c where pub2.rampid = c.rampid AND pub2.campaign_id = campaign AND pub2.channel = channel AND pub2.datetime > inputdatetime group by pub2.property

You can run this UDF using an SQL query such as the following:

select * from campaign_overlap(1, 'd', '2020-06-29', brands_rampid)

Note

When including a join clause in the UDF, the syntax "join table_name using(column_name)" may cause errors if you grant permission to the UDF or when you run it—even if your creation statement runs. The workaround is to run the UDF using syntax such as the following:

select property, count(distinct c.rampid) as overlap from pub2 
join customers c where pub2.rampid = c.rampid


In this section: