Table functions

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

Create table functions

To create a table function, use theCREATE TABLE FUNCTIONstatement. A table function contains a query that produces a table. The functionreturns the query result. The following table function takes anINT64parameter and uses this value inside aWHERE clause in a query over apublic dataset calledbigquery-public-data.usa_names.usa_1910_current:

CREATEORREPLACETABLEFUNCTIONmydataset.names_by_year(yINT64)AS(SELECTyear,name,SUM(number)AStotalFROM`bigquery-public-data.usa_names.usa_1910_current`WHEREyear=yGROUPBYyear,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:

CREATEORREPLACETABLEFUNCTIONmydataset.names_by_year_and_prefix(yINT64,zSTRING)AS(SELECTyear,name,SUM(number)AStotalFROM`bigquery-public-data.usa_names.usa_1910_current`WHEREyear=yANDSTARTS_WITH(name,z)GROUPBYyear,name);

Table parameters

This feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of theService Specific Terms. Pre-GA features are available "as is" and might have limited support. For more information, see thelaunch stage descriptions.

You can set TVF parameters to be tables. Following the table parametername, you must specify the required table schema explicitly, the same way thatyou specify the fields of a struct. The table argument that you pass to the TVFcan contain additional columns besides those specified in the parameter schema,and the columns can appear in any order.

The following table function returns a table that containstotal sales foritem_name from theorders table:

CREATETABLEFUNCTIONmydataset.compute_sales(ordersTABLE<salesINT64,itemSTRING>,item_nameSTRING)AS(SELECTSUM(sales)AStotal_sales,itemFROMordersWHEREitem=item_nameGROUPBYitem);

Parameter names

If a table function parameter matches the name of a table column, it can createan ambiguous reference. In that case, BigQuery interprets thename as a reference to the table column, not the parameter. The recommendedpractice is to use parameter names that are distinct from the names of anyreferenced table columns.

Use table functions

You can call a table function in any context where a table is valid. The followingexample calls themydataset.names_by_year function in theFROM clause ofaSELECT statement:

SELECT*FROMmydataset.names_by_year(1950)ORDERBYtotalDESCLIMIT5

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`ASsJOINmydataset.names_by_year(1950)ASnONn.name=s.word

You can also use a table function in asubquery:

SELECTARRAY(SELECTnameFROMmydataset.names_by_year(1950)ORDERBYtotalDESCLIMIT5)

When you call a table function that has a table parameter, you must use theTABLE keyword before the name of the table argument. The table argument canhave columns not listed in the table parameter schema:

CREATETABLEFUNCTIONmydataset.compute_sales(ordersTABLE<salesINT64,itemSTRING>,item_nameSTRING)AS(SELECTSUM(sales)AStotal_sales,itemFROMordersWHEREitem=item_nameGROUPBYitem);WITHmy_ordersAS(SELECT1ASsales,"apple"ASitem,0.99ASpriceUNIONALLSELECT2,"banana",0.49UNIONALLSELECT5,"apple",0.99)SELECT*FROMmydataset.compute_sales(TABLEmy_orders,"apple");/*-------------+-------+ | total_sales | item  | +-------------+-------+ | 6           | apple | +-------------+-------*/

List table functions

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

Delete table functions

To delete a table function, use theDROP TABLE FUNCTIONstatement:

DROPTABLEFUNCTIONmydataset.names_by_year

Authorize routines

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

Limitations

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

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

Quotas

For more information about table function quotas and limits, seeQuotas and limits.