BigQuery Create table valued function

A table-valued function (TVF), is a user-defined function that returns a table. You can use a table function anywhere that you can use a table. Table functions behave similarly to views, but a table function can take parameters.

Create table function example

    CREATE OR REPLACE TABLE FUNCTION mydataset.names_by_year(y INT64)
    AS
      SELECT year, name, SUM(number) AS total
      FROM `bigquery-public-data.usa_names.usa_1910_current`
      WHERE year = y
      GROUP BY year, name

Usage

    SELECT * FROM mydataset.names_by_year(1950)
    ORDER BY total DESC
    LIMIT 5

Using in joins

    SELECT *
      FROM `bigquery-public-data.samples.shakespeare` AS s
      JOIN mydataset.names_by_year(1950) AS n
      ON n.name = s.word

Using in subqueries

    SELECT ARRAY(
    SELECT name FROM mydataset.names_by_year(1950)
    ORDER BY total DESC
    LIMIT 5)

Explanation

Table valued functions has the following limitations

• The query body must be a SELECT statement and cannot modify anything. For example, DDL and DML statements are not allowed in table functions
• Parameters must be scalar values. Table functions in BigQuery cannot take tables as input parameters.

Syntax reference

CREATE [OR REPLACE] TABLE FUNCTION [IF NOT EXISTS]
  [[project_name.]dataset_name.]function_name
  ( [ function_parameter [, ...] ] )
  [RETURNS TABLE < column_declaration [, ...] > ]
  AS sql_query

function_parameter:
  parameter_name { data_type | ANY TYPE }

column_declaration:
  column_name data_type