When you call a function, specific rules may apply. You can also add theSAFE. prefix, which prevents functions from generating some types of errors.To learn more, see the next sections.

Function call rules

The following rules apply to all built-in GoogleSQL functions unlessexplicitly indicated otherwise in the function description:

• If an operand isNULL, the function result isNULL.
• For functions that are time zone sensitive, the default time zone,America/Los_Angeles, is used when a time zone isn't specified.

Lambdas

Syntax:

(arg[,...])->body_expression

arg->body_expression

Description

For some functions, GoogleSQL supports lambdas as builtin functionarguments. A lambda takes a list of arguments and an expression as the lambdabody.

• arg:
  • Name of the lambda argument is defined by the user.
  • No type is specified for the lambda argument. The type is inferred fromthe context.
• body_expression:
  • The lambda body can be any valid scalar expression.

SAFE. prefix

Syntax:

SAFE.function_name()

Description

If you begin a scalar function withtheSAFE. prefix, it will returnNULL instead of an error.TheSAFE. prefix only prevents errors from the prefixed functionitself: it doesn't prevent errors that occur while evaluating argumentexpressions. TheSAFE. prefix only prevents errors that occur because of thevalue of the function inputs, such as "value out of range" errors; othererrors, such as internal or system errors, may still occur. If the functiondoesn't return an error,SAFE. has no effect on the output.

Exclusions

• Operators, such as+ and=, don't support theSAFE. prefix. To prevent errors from adivision operation, useSAFE_DIVIDE.
• Some operators, such asIN,ARRAY, andUNNEST, resemble functions butdon't support theSAFE. prefix.
• TheCAST andEXTRACT functions don't support theSAFE.prefix. To prevent errors from casting, useSAFE_CAST.
• You can't append theSAFE. prefix to a function that containsalambda.

Example

In the following example, the first use of theSUBSTR function would normallyreturn an error, because the function doesn't support length arguments withnegative values. However, theSAFE. prefix causes the function to returnNULL instead. The second use of theSUBSTR function provides the expectedoutput: theSAFE. prefix has no effect.

SELECTSAFE.SUBSTR('foo',0,-2)ASsafe_outputUNIONALLSELECTSAFE.SUBSTR('bar',0,2)ASsafe_output;/*-------------+ | safe_output | +-------------+ | NULL        | | ba          | +-------------*/

Function hints

The following hints are available for GoogleSQL functions:

DISABLE_INLINE

function_name()@{DISABLE_INLINE=TRUE}

To disable other parts of a query from using the function as aninline expression, add the@{DISABLE_INLINE = TRUE} hint after ascalar function. This allows the function to be computed once insteadof each time another part of a query references it.

DISABLE_INLINE works with top-level functions.

You can't useDISABLE_INLINE with a few functions, includingthose that don't produce a scalar value andCAST. Although you can'tuseDISABLE_INLINE with theCAST function, you can use it with thefirst expression inside this function.

Examples

In the following example, inline expressions are enabled by default forx.x is computed twice, once by each reference:

SELECTSUBSTRING(CAST(xASSTRING),2,5)ASw,SUBSTRING(CAST(xASSTRING),3,7)ASyFROM(SELECTSHA512(z)ASxFROMt)

In the following example, inline expressions are disabled forx.x iscomputed once, and the result is used by each reference:

SELECTSUBSTRING(CAST(xASSTRING),2,5)ASw,SUBSTRING(CAST(xASSTRING),3,7)ASyFROM(SELECTSHA512(z)@{DISABLE_INLINE=TRUE}ASxFROMt)