1

given any function like e.g:

CREATE FUNCTION ds.fn(param ANY TYPE) RETURNS STRING AS (
    (SELECT 1/0)
);

Is there a way for handling errors when the statement fails and return a default value?

Note: My question is about any error that a select statement can throw and not only the arithmetic in the example above.

luisvenezian
  • 441
  • 5
  • 18

1 Answers1

1

You can make your function more robust using SAFE functions inside a UDF and also make it return default value if it fails like below.

CREATE FUNCTION ds.fn(param ANY TYPE) RETURNS AS ((
  SELECT IFNULL(SAFE_DIVIDE(1,0), 0)
));

Also, you can catch a error in a BigQuery script using BEGIN...EXCEPTION block:

CREATE OR REPLACE FUNCTION testset.fn(param ANY TYPE) RETURNS FLOAT64 AS ((
  SELECT 1/0
));

BEGIN
  SELECT testset.fn(10);
EXCEPTION WHEN ERROR THEN
  SELECT @@error.message;
END;
Jaytiger
  • 11,626
  • 2
  • 5
  • 15
  • thanks @Jaytiger, but the need is not specific for arithmetic errors, i am looking for a way for handling any kind of error that a select statement can throw – luisvenezian Jun 02 '22 at 15:21
  • If you want to catch error in multi statements, you can consider `BEGIN...EXCEPTION` in my updated answer. – Jaytiger Jun 02 '22 at 15:29
  • cool, that's exactly what i am trying to do but inside a function and then return a default value – luisvenezian Jun 02 '22 at 15:35