The following rules apply to all functions unless explicitly indicated otherwise in the function description:
- For functions that accept numeric types, if one operand is a floating point operand and the other operand is another numeric type, both operands are converted to FLOAT64 before the function is evaluated.
- If an operand is
NULL
, the result isNULL
, with the exception of the IS operator. - For functions that are time zone sensitive (as indicated in the function description), the default time zone, UTC, is used if a time zone is not specified.
SAFE. prefix
Syntax:
SAFE.function_name()
Description
If you begin a function with the SAFE.
prefix, it will return NULL
instead
of an error. The SAFE.
prefix only prevents errors from the prefixed function
itself: it does not prevent errors that occur while evaluating argument
expressions. The SAFE.
prefix only prevents errors that occur because of the
value of the function inputs, such as "value out of range" errors; other
errors, such as internal or system errors, may still occur. If the function
does not return an error, SAFE.
has no effect on the output. If the function
never returns an error, like RAND
, then SAFE.
has no effect.
Operators, such as +
and =
, do not support the SAFE.
prefix. To prevent errors from a division
operation, use SAFE_DIVIDE. Some operators,
such as IN
, ARRAY
, and UNNEST
, resemble functions, but do not support the
SAFE.
prefix. The CAST
and EXTRACT
functions also do not support the
SAFE.
prefix. To prevent errors from casting, use
SAFE_CAST.
Example
In the following example, the first use of the SUBSTR
function would normally
return an error, because the function does not support length arguments with
negative values. However, the SAFE.
prefix causes the function to return
NULL
instead. The second use of the SUBSTR
function provides the expected
output: the SAFE.
prefix has no effect.
SELECT SAFE.SUBSTR('foo', 0, -2) AS safe_output UNION ALL
SELECT SAFE.SUBSTR('bar', 0, 2) AS safe_output;
+-------------+
| safe_output |
+-------------+
| NULL |
| ba |
+-------------+
Supported functions
BigQuery supports the use of the SAFE.
prefix with most scalar
functions that can raise errors, including STRING functions,
math functions, DATE functions,
DATETIME functions, and
TIMESTAMP functions. BigQuery does not support
the use of the SAFE.
prefix with aggregate,
analytic, or user-defined functions.
Calling persistent user-defined functions (UDFs)
After creating a persistent UDF, you can call it as you would any other function, prepended with the name of the dataset in which it is defined as a prefix.
Syntax
[`project_name`].dataset_name.function_name([parameter_value[, ...]])
To call a UDF in a project other than the project that you are using to run
the query, project_name
is required.
Examples
The following example creates a UDF named multiply_by_three
and calls it
from the same project.
CREATE FUNCTION my_dataset.multiply_by_three(x INT64) AS (x * 3);
SELECT my_dataset.multiply_by_three(5) AS result; -- returns 15
The following example calls a persistent UDF from a different project.
CREATE `other_project`.other_dataset.other_function(x INT64, y INT64)
AS (x * y * 2);
SELECT `other_project`.other_dataset.other_function(3, 4); --returns 24