How To Write Dynamic Queries In Snowflake User Defined Functions

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_

Solution 1:

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:

createOR 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' )
unionallselect val3 from woop WHERE col2 = id and ( Column_Name ='col2'and tbl_name ='woop' )  
$$
;

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

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

Solution 2:

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

Solution 3:

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