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 {#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 [, ...]>

The table argument can specify a value table, in which each row is a single column of a specific type. To specify a value table as an argument, include only the data_type, leaving out the column_name:

parameter_name TABLE<data_type>

In many cases, the data_type of the single column in the value table is a protocol buffer; for example:

CREATE TEMP TABLE FUNCTION AggregatedMovieLogs(
  TicketPurchases TABLE<analysis_conduit.codelab.MovieTicketPurchase>)

The function body can refer directly to fields within the proto.

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.