Table-valued functions

GoogleSQL for SecOps supports table-valued functions (TVFs). A TVF returns an entire output table instead of a single scalar value, and appears in the FROM clause like a table subquery.

Create a TVF

You can create a TVF using the following syntax:

CREATE
  [ OR REPLACE ]
  { TEMPORARY | TEMP } TABLE FUNCTION
  [ IF NOT EXISTS ]
  function_name ( [ function_parameter  [, ...] ] )
  [ RETURNS TABLE < column_declaration [, ...] > ]
  [ { AS query | LANGUAGE language_name AS string_literal } ]

function_parameter:
  parameter_name { data_type | ANY TYPE }

column_declaration:
  column_name data_type

  • CREATE ... TABLE FUNCTION: Creates a new table-valued function function. A function can have zero or more function parameters.

    • TEMPORARY or TEMP: Indicates that the function is temporary, meaning that it exists for the lifetime of the session.

  • OR REPLACE: Replaces any function with the same name if it exists. Can't appear with IF NOT EXISTS.

  • IF NOT EXISTS: If any function exists with the same name, the CREATE statement has no effect. Can't appear with OR REPLACE.

  • function_parameter: A parameter for the function.

    • parameter_name: The name of the parameter.

    • data_type: A GoogleSQL data type.

    • ANY TYPE: The function will accept an argument of any type for this function parameter. If more than one parameter includes ANY TYPE, a relationship isn't enforced between these parameters when the function is defined. However, if the type of argument passed into the function at call time is incompatible with the function definition, this will result in an error.

      ANY TYPE is a templated function parameter.

  • RETURNS TABLE: Specifies the schema of the table that a table-valued function returns as a comma-separated list of column_name and TYPE pairs. If RETURNS TABLE is absent, GoogleSQL infers the output schema from the AS query statement in the function body.

  • AS query: If you want to create a SQL TVF, specifies the SQL query to run.

  • LANGUAGE ... AS: If you want to create an external TVF, specifies the language and code to use. language_name represents the name of the language, such as js for JavaScript. string_literal represents the code that defines the function body.

You can create a public or privately-scoped TVF in a module. To learn more, see Modules.

Specify TVF arguments

When a TVF with function parameters is called, arguments must be passed in for all function parameters that don't have default values. An argument can be of any supported GoogleSQL type or table, but must be coercible to the related function parameter's type.

Specify a table argument the same way you specify the fields of a STRUCT.

parameter_name TABLE<column_name data_type [, ...]>

When you pass an input table for a TABLE<column_name data_type [, ...]> parameter, GoogleSQL resolves the input table columns as follows:

  • Matched by name, not position: GoogleSQL matches columns from the input table to the columns in the parameter definition case-insensitively by name, in any order. The input table must include all required columns specified in the parameter definition and can't contain duplicate column names that match a required column.
  • Type matching and coercion: Each matched column in the input table must have the same data type as, or a type implicitly coercible to, the corresponding column in the parameter definition.
  • Extra columns: For TVFs created with CREATE TABLE FUNCTION, the input table can include additional columns not listed in the parameter definition, and GoogleSQL ignores those extra columns inside the function body. Built-in or catalog-provided TVFs can disallow extra input columns in their signature. When extra input columns are disallowed, passing an extra column returns an error.

Example

The following example implements a pair of TVFs that define parameterized views of a range of rows from the Customer table. The first returns all rows for a range of CustomerIds; the second calls the first function and applies an additional filter based on CustomerType.

CREATE TEMP TABLE FUNCTION CustomerRange(MinID INT64, MaxID INT64)
AS (
  SELECT *
  FROM Customer
  WHERE CustomerId >= MinId AND CustomerId <= MaxId
);

CREATE TEMP TABLE FUNCTION CustomerRangeWithCustomerType(
  MinId INT64,
  MaxId INT64,
  customer_type ads.boulder.schema.CustomerType)
AS (
  SELECT *
  FROM CustomerRange(MinId, MaxId)
  WHERE type = customer_type
);

Call a TVF

To call a TVF, see Table function calls.