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

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

Usage

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

Using in joins

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

Using in subqueries

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

Explanation

Table valued functions has the following limitations

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

Syntax reference

CREATE [OR REPLACE] TABLE FUNCTION [IF NOT EXISTS]\n  [[project_name.]dataset_name.]function_name\n  ( [ function_parameter [, ...] ] )\n  [RETURNS TABLE < column_declaration [, ...] > ]\n  AS sql_query\n\nfunction_parameter:\n  parameter_name { data_type | ANY TYPE }\n\ncolumn_declaration:\n  column_name data_type