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

Authorized table functions

Authorized functions let you share query results with particular users or groups without giving those users or groups access to the underlying tables. For example, an authorized function can compute an aggregation over data or look up a table value and use that value in a computation.

For more information, see Creating authorized functions.

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.