Function calls

When you call a function, specific rules may apply. 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 isn't specified.

Chained function calls

Writing nested expressions in GoogleSQL is common, particularly when you're cleaning or transforming data. Deeply nested expressions can be hard to read and maintain.

Here's an example of an expression with deep nesting. The nesting makes it difficult to read:

SELECT
  REPLACE(
    REPLACE(
      REPLACE(
        REPLACE(
          REPLACE('one two three four five', 'one', '1'),
          'two', '2'),
        'three', '3'),
      'four', '4'),
    'five', '5');

Here is the same example rewritten using chained function syntax:

SELECT
  ('one two three four five')
  .REPLACE('one', '1')
  .REPLACE('two', '2')
  .REPLACE('three', '3')
  .REPLACE('four', '4')
  .REPLACE('five', '5');

Chained function calls provide a syntax for simplifying nested function calls. Chained function calls have the following properties:

• Chained function calls consist of functions connected together with a . character.
• Each function in the chain must meet certain requirements.
• Each function in the chain uses the output from the previous function as its first argument.
• If the chain starts from a column name (or other identifier), that initial argument must be surrounded by () characters, for example: (x).UPPER(). Parentheses aren't required on the input for other cases.

Chained function calls are generally easier to read, understand, and maintain than deeply nested function calls because they're applied in the order in which they're written.

Chained function requirements

You can write function calls in chained call syntax if the functions meet these requirements:

• The function must use standard function call syntax, using comma-separated arguments. Function-like syntaxes that include special-case keywords, such as CAST(value AS type), aren't included.
• The function must have at least one argument. The first argument becomes the chained input, and must meet the following requirements:
  • It must be an expression. It can't be a table, connection, model, descriptor, or other non-expression argument type.
  • It can't have an AS alias (as in value AS alias).

There are a few additional special cases. Chained function calls are allowed for these functions:

• Aggregate functions with standard modifiers like DISTINCT, ORDER BY, etc. You write the modifiers inside the parentheses with their usual syntax. For example, you write COUNT(DISTINCT x) as (x).COUNT(DISTINCT).
• FLATTEN (and other functions that do implicit flattening)

Chained function calls aren't allowed for these functions:

• GROUPING

Example chained function calls

The following examples show the chained function call equivalent of some standard syntax calls:

UPPER(x)
(x).UPPER()  # Chained function call equivalent; the x must be within ()

SUBSTR(x, 1, 4)
(x).SUBSTR(1, 4)  # Chained function call equivalent

STRPOS(x, 'pattern string')
(x).STRPOS('pattern string')  # Chained function call equivalent

ARRAY_CONCAT(array1, array2)
(array1).ARRAY_CONCAT(array2)  # Chained function call equivalent

Here are chained function call examples with multiple function calls:

SELECT "Two birds and one mouse"
  .REPLACE("bird", "dog")
  .REPLACE("mouse", "cat") AS result;

/*----------------------+
 |      result          |
 +----------------------+
 | Two dogs and one cat |
 +----------------------*/

The following examples result in errors because the function being called doesn't meet the necessary requirements.

CAST(x AS INT64)
(x).CAST(AS INT64)  # Error: CAST syntax isn't supported in chained function calls.

GROUPING(x)
(x).GROUPING()  # Error: The argument isn't an expression.