"Unknown function" error when attempting to run Java UDTF

Question

I've written a Java based UDTF in Snowflake with a precompiled jar. I was able to upload the jar into Snowflake and create the function successfully, so I'm fairly sure Snowflake is happy with the contract being met in my Java code.

However, when I actually try to invoke the function, I receive the following:

SQL compilation error: Unknown function [function_name]

I've confirmed the function exists with "show user functions;" and I've confirmed that the argument types are correct.

To call the function I'm simply doing

select custom_function('A', 'B'); 

and then I get the error listed above. That type of invocation seems acceptable since examples like that are in the documentation, so I'm completely stumped as to what is going on. Hoping someone can help point me in the right direction.

Edit: I didn't indicate it above, but I do have both the database and schema that the UDTF was created in set on my connection to Snowflake. I've tried both in the Snowflake UI and via VS Code and in both cases those values were set appropriately.

Thanks!

Answer 1

The issue is you are calling this table function as a scalar function and Snowflake is searching for a scalar function with the name "custom_function". You have to instead call the function like this: select * from table(custom_function('A', 'B')). See the Java UDTF Snowflake documentation.

Answer 2

Functions are created in a specific database and schema. Call USE DATABASE and USE SCHEMA, or call your function with a fully qualified name, to make sure your current database/schema context is not different. Or make sure your session variables point to the right database and schema, if you call it through an application. Example:

use database mydb;
use schema myschema;

select * from table(custom_function('A', 'B'));