Function calls

Stay organized with collections Save and categorize content based on your preferences.

When you call a function, specific rules may apply. You can also add the SAFE. 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 unless explicitly indicated otherwise in the function description:

  • If an operand is NULL, the function result is NULL.
  • For functions that are time zone sensitive, the default time zone, UTC, is used when a time zone is not specified.

Named arguments

named_argument => value

You can provide parameter arguments by name when calling some functions and procedures. These arguments are called named arguments. An argument that is not named is called a positional argument.

  • Named arguments are optional, unless specified as required in the function signature.
  • Named arguments don't need to be in order.
  • You can specify positional arguments before named arguments.
  • You cannot specify positional arguments after named arguments.
  • An optional positional argument that is not used doesn't need to be added before a named argument.

Examples

These examples reference a function called CountTokensInText, which counts the number of tokens in a paragraph. The function signature looks like this:

CountTokensInText(paragraph STRING, tokens ARRAY<STRING>, delimiters STRING)

CountTokensInText contains three arguments: paragraph, tokens, and delimiters. paragraph represents a body of text to analyze, tokens represents the tokens to search for in the paragraph, and delimiters represents the characters that specify a boundary between tokens in the paragraph.

This is a query that includes CountTokensInText without named arguments:

SELECT token, count
FROM CountTokensInText(
  'Would you prefer softball, baseball, or tennis? There is also swimming.',
  ['baseball', 'football', 'tennis'],
  ' .,!?()')

This is the query with named arguments:

SELECT token, count
FROM CountTokensInText(
  paragraph => 'Would you prefer softball, baseball, or tennis? There is also swimming.',
  tokens => ['baseball', 'football', 'tennis'],
  delimiters => ' .,!?()')

If named arguments are used, the order of the arguments doesn't matter. This works:

SELECT token, count
FROM CountTokensInText(
  tokens => ['baseball', 'football', 'tennis'],
  delimiters => ' .,!?()',
  paragraph => 'Would you prefer softball, baseball, or tennis? There is also swimming.')

You can mix positional arguments and named arguments, as long as the positional arguments in the function signature come first:

SELECT token, count
FROM CountTokensInText(
  'Would you prefer softball, baseball, or tennis? There is also swimming.',
  tokens => ['baseball', 'football', 'tennis'],
  delimiters => ' .,!?()')

This doesn't work because a positional argument appears after a named argument:

SELECT token, count
FROM CountTokensInText(
  paragraph => 'Would you prefer softball, baseball, or tennis? There is also swimming.',
  ['baseball', 'football', 'tennis'],
  delimiters => ' .,!?()')

If you want to use tokens as a positional argument, any arguments that appear before it in the function signature must also be positional arguments. If you try to use a named argument for paragraph and a positional argument for tokens, this will not work.

-- This doesn't work.
SELECT token, count
FROM CountTokensInText(
  ['baseball', 'football', 'tennis'],
  delimiters => ' .,!?()',
  paragraph => 'Would you prefer softball, baseball, or tennis? There is also swimming.')

-- This works.
SELECT token, count
FROM CountTokensInText(
  'Would you prefer softball, baseball, or tennis? There is also swimming.',
  ['baseball', 'football', 'tennis'],
  delimiters => ' .,!?()')

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 doesn't 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 doesn't return an error, SAFE. has no effect on the output.

Exclusions

  • Operators, such as + and =, don't 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 don't support the SAFE. prefix.
  • The CAST and EXTRACT functions don't 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 doesn't 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, TIMESTAMP functions, and JSON functions. BigQuery does not support the use of the SAFE. prefix with aggregate, window, 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