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.

Create table functions

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
);

To filter in other ways, you can pass multiple parameters to a table function. The following table function filters the data by year and name prefix:

CREATE OR REPLACE TABLE FUNCTION mydataset.names_by_year_and_prefix(
  y INT64, z STRING)
AS (
  SELECT year, name, SUM(number) AS total
  FROM `bigquery-public-data.usa_names.usa_1910_current`
  WHERE
    year = y
    AND STARTS_WITH(name, z)
  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.

Use table functions

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)

List table functions

Table functions are a type of routine. To list all of the routines in a dataset, see List routines.

Delete table functions

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

DROP TABLE FUNCTION mydataset.names_by_year

Authorize routines

You can authorize table functions as routines. Authorized routines let you share query results with specific users or groups without giving them access to the underlying tables that generated the results. For example, an authorized routine can compute an aggregation over data or look up a table value and use that value in a computation. For more information, see Authorized routines.

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.

  • Table functions must be stored in the same location as the tables they reference.

Quotas

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