cancel
Showing results for 
Search instead for 
Did you mean: 
Community Discussions
cancel
Showing results for 
Search instead for 
Did you mean: 

Can we pass parameters thru SQL UDF's?

TrevorC
New Contributor III


Is it possible to pass a parameter to a SQL UDF to another SQL UDF that is called by the first SQL UDF?

Below is an example where I would like to call tbl_filter() from tbl_func() by passing the tbl_func.a_val parameter to tbl_filter(). 

Obviously, I could just implement the logic in one function, but in my use case that means repeating the "tbl_filter()" part of the query over and over in different versions of "tbl_func()", which defeats the purpose of packaging code in functions. Ideally, there would be a single version of "tbl_filter()" to maintain that could be called from a variety of different versions of "tbl_func()". 

 

CREATE OR REPLACE TEMPORARY VIEW test_tbl AS
WITH a AS
(SELECT explode(sequence(1, 10)) AS a),
b AS
(SELECT explode(sequence(50, 60)) AS b)
SELECT * FROM a CROSS JOIN b;

CREATE OR REPLACE TEMPORARY FUNCTION tbl_filter(a_val INT)
    RETURNS TABLE(a INT, b INT)
    RETURN
    SELECT * FROM test_tbl tf
    WHERE tf.a = tbl_filter.a_val;

CREATE OR REPLACE TEMPORARY FUNCTION tbl_func(a_val INT, b_val INT)
    RETURNS TABLE(a INT, b INT)
    RETURN
    SELECT * FROM tbl_filter(tbl_func.a_val) tf
    WHERE tf.b = tbl_func.b_val;

-- This executes
-- select * from tbl_filter(1);

-- This does not: Error in SQL statement: AnalysisException: could not resolve `tbl_filter` to a table-valued function.
select * from tbl_func(1, 60);
   
1 ACCEPTED SOLUTION

Accepted Solutions

TrevorC
New Contributor III

Thanks, Kaniz, that does not suit my use case, but I got an answer to this on StackOverflow:
In Databricks Workbook using Spark SQL, how to pass parameters thru SQL UDF functions? - Stack Overf...

The issue was the use of temporary functions. 

View solution in original post

2 REPLIES 2

Kaniz
Community Manager
Community Manager

Hi @TrevorC, You can pass a parameter from one SQL User-Defined Function (UDF) to another. 

 

Let’s break down your example and address the issue.

  1. tbl_filter() is a UDF that takes an input parameter a_val and returns a table.
  2. tbl_func() is another UDF that also takes parameters a_val and b_val, and returns a table.
  3. In tbl_func(), you want to call tbl_filter() by passing the a_val parameter.

The error you encountered (Error in SQL statement: AnalysisException: could not resolve 'tbl_filter' to a table-valued function.) occurs because the tbl_filter() function is not recognized within the context of tbl_func().

 

To achieve your goal, you can use a common table expression (CTE) or a subquery to compute the result of tbl_filter() before using it in tbl_func().

 

TrevorC
New Contributor III

Thanks, Kaniz, that does not suit my use case, but I got an answer to this on StackOverflow:
In Databricks Workbook using Spark SQL, how to pass parameters thru SQL UDF functions? - Stack Overf...

The issue was the use of temporary functions. 

Welcome to Databricks Community: Lets learn, network and celebrate together

Join our fast-growing data practitioner and expert community of 80K+ members, ready to discover, help and collaborate together while making meaningful connections. 

Click here to register and join today! 

Engage in exciting technical discussions, join a group with your peers and meet our Featured Members.