Procedures and table functions
A temporary function returns one value and lasts for one script. When a script needs several statements run together, or a query with parameters that other queries read, you store a procedure or a table function in a dataset.
How it works
CREATE PROCEDURE saves a block of statements under a name, and CALL runs the block. A procedure has no return value of its own. It hands values back through its OUT and INOUT arguments. CREATE TABLE FUNCTION saves a query under a name. You call it in a FROM, like a table whose rows depend on its arguments.
- BigQuery has temporary functions, and no temporary procedure or table function.
CREATE TEMP PROCEDUREis refused. A procedure or a table function lives in a dataset, so its name starts with one:learn.mot.busiest_days. WriteCREATE OR REPLACE, so that running the script again replaces the routine instead of failing on the one it made before. In your own project the routine stays in its dataset until you drop it. CREATE PROCEDURE learn.mot.name(IN a STRING, OUT b INT64) BEGIN ... END;lists the parameters, each with a mode and a type.INis the default and brings a value in.OUTsends a value back to the caller.INOUTdoes both. The body is a block of statements, and it canDECLAREvariables of its own.CALL learn.mot.name('FORD', total);runs it, and each argument goes to the parameter in the same position. AnOUTorINOUTargument has to be a variable you declared before the call. A literal there fails withProcedure OUT/INOUT argument must be a variable. Too few or too many arguments fail withexpects 3 argument(s), 2 provided.- Inside the body an
OUTparameter starts asNULL, whatever the variable held before the call. AnINOUTparameter starts with the value of the variable. The caller's variable changes when the body ends without an error. The body cannot see the caller's variables, only its own parameters. - A
CALLhas no value of its own. It runs the statements in the body of the procedure, and a value for the script to keep goes back through anOUTorINOUTargument. The result of a script is the result of the last statement that ran. CREATE TABLE FUNCTION learn.mot.name(p STRING) RETURNS TABLE<col STRING, n INT64> AS (SELECT ...);stores a query. TheRETURNS TABLElist is optional. When it is there, the query has to return every listed column by name, with a type that fits. A different type fails atCREATE, and so does a name the query does not return.- You read the function in a
FROMwith its arguments, as inFROM learn.mot.name('FORD'). After that it is a table: filter it, join it, aggregate it. Call it twice with different arguments and join the two to compare. Without its argument the call fails withSignature requires at least 1 argument, found 0 arguments. In a select list it fails withTable-valued function is not expected here. - A filter on the rows of a table function reaches the table inside it, as it does through a view.
fact_mot_testrequires a filter oncompleted_date. A function whose query has none still runs when the query that reads it filters on that column, and it reads the same bytes as the same query written inline. A constant argument that the function compares withcompleted_dateprunes in the same way. An argument computed by reading a table does not, and on this table the query fails with the partition filter error. - BigQuery bills at least 10 MiB for each table a statement reads, however many times the statement reads it. A join of two calls of a function over one table is billed 10 MiB, and three statements that each read the table are billed three times.
- A temporary function gives one value for each row, inside an expression. A table function is a query with parameters that other queries read as a table. A procedure is for several statements, such as changing tables or setting variables. A procedure cannot stand in an expression or a
FROM: BigQuery saysProcedure is not supported in this context.
Common mistake
An OUT parameter does not carry the old value into the body. A procedure that adds to it, as in SET total = total + 1, adds to NULL and returns NULL, with no error.
Worked example
DECLARE first_day DATE;
DECLARE last_day DATE;
CREATE OR REPLACE PROCEDURE learn.mot.test_span(IN wanted_make STRING, OUT first_day DATE, OUT last_day DATE)
BEGIN
SET (first_day, last_day) = (
SELECT AS STRUCT MIN(completed_date), MAX(completed_date)
FROM `learn.mot.fact_mot_test`
WHERE make = wanted_make
AND completed_date BETWEEN '2024-01-01' AND '2024-03-31'
);
END;
CREATE OR REPLACE TABLE FUNCTION learn.mot.tests_by_day(wanted_make STRING)
RETURNS TABLE<completed_date DATE, tests INT64> AS (
SELECT completed_date, COUNT(*) AS tests
FROM `learn.mot.fact_mot_test`
WHERE make = wanted_make
AND completed_date BETWEEN '2024-01-01' AND '2024-03-31'
GROUP BY completed_date
);
CALL learn.mot.test_span('HONDA', first_day, last_day);
SELECT completed_date, tests
FROM learn.mot.tests_by_day('HONDA')
WHERE completed_date IN (first_day, last_day)
ORDER BY completed_date;The procedure test_span takes a make and gives back two dates through its OUT parameters: the first and the last day with a test in the first three months of 2024.
The table function tests_by_day takes a make and returns one row for each day, with the number of tests.
CALL fills the variables first_day and last_day. The call has no value of its own, so the dates come back only through the variables.
The last query reads the table function in its FROM and keeps the two days that the procedure found. The variables work in WHERE like any other value.
The procedure ran statements and handed back two values. The function handed back rows.