Table functions

A table function, also called 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.

Creating a table function

To create a table function, use the CREATE TABLE FUNCTION statement. A table function contains a query that produces a table. The function returns the query result. The following table function takes an INT64 parameter and uses this value inside a WHERE clause in a query over a public dataset called bigquery-public-data.usa_names.usa_1910_current:

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

Parameter names

If a table function parameter matches the name of a table column, it can create an ambiguous reference. In that case, BigQuery interprets the name as a reference to the table column, not the parameter. The recommended practice is to use parameter names that are distinct from the names of any referenced table columns.

Using a table function

You can call a table function in any context where a table is valid. The following example calls the mydataset.names_by_year function in the FROM clause of a SELECT statement:

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

The results look like the following:

+------+--------+-------+
| year |  name  | total |
+------+--------+-------+
| 1950 | James  | 86447 |
| 1950 | Robert | 83717 |
| 1950 | Linda  | 80498 |
| 1950 | John   | 79561 |
| 1950 | Mary   | 65546 |
+------+--------+-------+

You can join the output from a table function with another table:

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

You can also use a table function in a subquery:

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

Deleting a table function

To delete a table function, use the DROP TABLE FUNCTION statement:

DROP TABLE FUNCTION mydataset.names_by_year

Limitations

  • Parameters must be scalar values. Table functions in BigQuery cannot take tables as input parameters.

  • The query body must be a SELECT statement and cannot modify anything. For example, data definition language (DDL) and data manipulation language (DML) statements are not allowed in table functions. If you need side-effects, consider writing a procedure instead.

Quotas

For more information about table function quotas and limits, see Quotas and limits.