1

I want to use SQL query in udf and get some column value from it and have some calculation through it. I am not able to write SQL inside permanent udf function. I want something like that ->

create function dataset.fun(j int64)

language js as r"""

select a from dataset.table1 where condition;   
#let's suppose column "a" is int64

return (j+a)
"""

How to correct it? What is the correct way to write this UDF function?

And if it is not possible through js, Kindly suggest me the other way.

Thank you in advance.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
D7-PO
  • 21
  • 4

1 Answers1

-1

As mentioned in documentation, you can create UDF's like this-

CREATE TEMP FUNCTION multiplyInputs(x FLOAT64, y FLOAT64)
RETURNS FLOAT64
LANGUAGE js AS r"""
  return x*y;
""";

WITH numbers AS
  (SELECT 1 AS x, 5 as y
  UNION ALL
  SELECT 2 AS x, 10 as y
  UNION ALL
  SELECT 3 as x, 15 as y)
SELECT x, y, multiplyInputs(x, y) as product
FROM numbers;

edit as per comment below.

Yes you can create a perm function like this

CREATE or REPLACE FUNCTION YourDataset.multiplyInputs(x FLOAT64, y FLOAT64)
RETURNS FLOAT64
LANGUAGE js AS r"""
select number as p from `YourDataset.Table` where Column1='qwerty'
  return p*x*y;
""";
Prany
  • 2,078
  • 2
  • 13
  • 31
  • Hi Prany, this will create a temporary function(UDF), but I want to create permanent UDF, so that I can call this UDF inside some views or procedures ( while creating views or procedures ). Please guide me on this. – D7-PO Nov 01 '21 at 21:24
  • Added one more example of creating permanent functions. Mark this an answer if this is what you're looking for – Prany Nov 02 '21 at 08:16