BigQuery: define variables inside a function

user_809141 0
Viewed 2029

I am trying to define a simple Standard SQL function where the input is multiplied by a constant defined inside the function.

CREATE or REPLACE FUNCTION Test(b FLOAT64) 
RETURNS FLOAT64
AS
DECLARE var FLOAT64 = 0.5;
RETURN (b * var)
;

the syntax error is

Syntax error: Expected "(" or string literal but got keyword DECLARE

How do you define variables inside functions?

google-bigquery
2 Answers

Another option for BigQuery Standard SQL using SQL UDF

#standardsql
create or replace function test(b float64) 
returns float64 as ((
with variables as (
  select 0.5 as var1, 1.5 as var2
)
select b * var1 + var2
from variables
));

As you can see - you can define variables CTE with all needed variables in one row

user_52219440

Variables are not supported inside SQL functions in BigQuery. One of the options would be to declare and set the variable in the beginning of your code. Then pass it as an argument to a function.

Another options would be to use Javascript Function:

CREATE TEMP FUNCTION customGreeting(b FLOAT64)
RETURNS FLOAT64
LANGUAGE js AS """
  var f = 0.5;
  return f * b;
""";
user_134735250
Related