User Defined Function not found

Question

I have created a permanent UDF function to validate emails like this:

create or replace function 
`project-name`.udf_library.is_valid_email(text STRING)
returns Bool
as (REGEXP_CONTAINS(text, r"valid_regex"));

and have tested it with the following query and works like a charm:

with emails as
(select '[email protected]' as email
union all
select 'foobar' as email
union all
select '[email protected]' as email
union all
select '"john..doe"@example.org' as email
union all
select 'i_like_underscore@but_its_not_allow_in_this_part.example.com' as email)
select email, `project-name`.udf_library.is_valid_email(email) as validEmail from emails

Row email                                                   validEmail 
1   [email protected]                                           true
2   foobar                                                       false
3   [email protected]                true
4   "john..doe"@example.org                                       true
5   i_like_underscore@but_its_not_allow_in_this_part.example.com false

But when I query a table and try to use the function like this

SELECT email, `project-name`.udf_library.is_valid_email(email) as validEmail 
FROM `project-name.Mydataset.MyTable`

I get this:

Function not found: project-name.udf_library.is_valid_email at [1:15]

It does work if I create it as temp function but that defeats the whole purpose of having permanent UDFs

Any ideas?

Thanks

Answer 1

Please check location of dataset project-name.Mydataset and project-name.udf_library, they have to sit in same region for your last query to work.

The query using WITH to prepare data works because it is routed to the region where the UDF sits. The last query probably got routed to where the data is.