How to write dynamic queries in snowflake user defined functions

Question

I am writing the below function in snowflake.

create OR REPLACE function myTestFunc(tbl_name VARCHAR,  Column_Name varchar, id VARCHAR)
  returns varchar
  as
  $$
  select Column_Name from tbl_name WHERE Column_Name=id
  $$
  ;

How to pass the table and column names as input parameters and use those parameter as table and column names within the query. Here the parameters are treated as string literals but not as a table or column name. Is there any way to achieve this within user defined functions.

Thanks

score 2 · Answer 1 · answered Jun 16 '20 at 13:28

There's one basic thing you need to understand about Snowflake UDFs to set your expectations correct - it's not really a function in the sense of programming language, but more like a piece of SQL code that is unwrapped at the point SQL is getting executed. So at the point your run your SQL all the UDFs used there are replaced with their actual code and then it runs.

This puts some limitations on what can and cannot be done using UDFs and dynamic queries is one of the things that don't make sense when you look at UDFs from this angle.

As Gokhan said above - the right way to go with dynamic queries is Snowflake procedures

Gokhan Atil · Answer 2 · 2020-06-16T09:21:29.230

0

You can use Snowflake PROCEDURE which supports snowflake object to execute dynamic queries.

https://docs.snowflake.com/en/sql-reference/stored-procedures-usage.html

If the tables and columns are limited, you may use this approach:

create OR REPLACE function myTestFunc(tbl_name VARCHAR,  Column_Name varchar, id VARCHAR)
returns number
as
$$
select val1 from test WHERE col1 = id and ( Column_Name = 'col1' and tbl_name = 'test' )
union all
select val3 from woop WHERE col2 = id and ( Column_Name = 'col2' and tbl_name = 'woop' )  
$$
;

select myTestFunc( 'test','col1','Jack' );

select myTestFunc( 'woop','col2','Jack' );

edited Jun 16 '20 at 09:21

answered Jun 16 '20 at 08:35

Gokhan Atil

9,278
2
11
24

Hi Gokhan Atil, I am able to do it in procedures using snowflake.createStatement. My requirement is to acheive the same through functions. I have gone through the JavaScript UDTFs url provided by you. In this, I didn't find any examples that suit my requirement. Is there any other way. – inzero Jun 16 '20 at 08:58
Hi inzero! It's not possible to do it with SQL UDF. I can write a very simple sample to demonstrate the JS UDTFs, but do you want a function to return a single value (not a dataset)? – Gokhan Atil Jun 16 '20 at 09:01
Yes. the query used in my function always return one value. Sorry for asking these very basic things. I am new to snowflake. – inzero Jun 16 '20 at 09:08
No, it's not a basic thing but I can't say it's a good approach. By the way I was wrong about JavaScript UDTF - as you need dynamic SQL, you should use Snowflake PROCEDURE. If the tables and columns are limited, you may try to create a SQL UDF with unions. – Gokhan Atil Jun 16 '20 at 09:14
Okay. I cannot use procedures because i want to use the result of this function in another query. – inzero Jun 16 '20 at 09:19
added a sample to explain what I meant by using UNIONs – Gokhan Atil Jun 16 '20 at 09:21

score 0 · Answer 3 · answered Jun 16 '20 at 15:01

This is a way in which you can make your query dynamic. I tested it and it is working.

CREATE OR REPLACE PROCEDURE MY_TEST_FUNC(TBL_NAME VARCHAR,  COLUMN_NAME varchar, ID VARCHAR)
RETURNS STRING  
LANGUAGE JAVASCRIPT
AS
$$
var sql_command = "select " + COLUMN_NAME + " from " + TBL_NAME + " WHERE " + COLUMN_NAME + " = " + ID + ""; 
    try {
        var result_set = snowflake.execute ({sqlText: sql_command});
        while (result_set.next())  {
        v_col_name = result_set.getColumnValue(1);
        }
        return "Success::" + v_col_name;   // Return a success/error indicator.
        }
    catch (err)  {
        return "Failed: "+ sql_command + err;   // Return a success/error indicator.
        }
$$;

You can call the function like so:

CALL MY_TEST_FUNC('DB_NAME.SCHEMA_NAME.TABLE_NAME', 'COLUMN_NAME', 'ID_VALUE');

Thanks

How to write dynamic queries in snowflake user defined functions

3 Answers3

Linked