User-defined functions can be written in JavaScript or SQL in BigQuery. These functions can be called in queries to obtain results. The following are the supported datatypes that can be passed to and returned by the functions:

• ARRAY
• BOOL
• BYTES
• DATE
• FLOAT64
• STRING
• STRUCT
• TIMESTAMP

The following is a simple function written in JavaScript to return the sum of two numbers, and it is used in the query. This query passes the tip_amount and tolls_amount values for each row from the table to the function and gets the sum:

#standardSQL
CREATE TEMPORARY FUNCTION GetOtherCharges(tipamount FLOAT64, tollsamount FLOAT64)
RETURNS INT64
LANGUAGE js AS """
  return tipamount + tollsamount;
""";

SELECT vendor_id, GetOtherCharges( tip_amount, tolls_amount )
FROM `bigquery-public-data.new_york.tlc_green_trips_2013`

Here is an SQL user-defined function that will return max for two values passed to it. The entire body of the function must be within (). This SQL function will return the max between two columns passed to it for each row:

#standardSQL
CREATE TEMPORARY FUNCTION GetOtherCharges(tipamount FLOAT64, tollsamount FLOAT64)
AS ( 
      (
SELECT MAX(VAL1) FROM (
SELECT tipamount AS VAL1
UNION ALL
SELECT tollsamount AS VAL1
            )
      )
);

SELECT vendor_id, GetOtherCharges( tip_amount, tolls_amount )
FROM `bigquery-public-data.new_york.tlc_green_trips_2013`