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

MMV · Accepted Answer · 2020-06-16 13:28:36Z

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

answered Jun 16, 2020 at 13:28

MMV

9906 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gokhan Atil · Accepted Answer · 2020-06-16 09:21:29Z

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, 2020 at 9:21

answered Jun 16, 2020 at 8:35

Gokhan Atil

10.6k2 gold badges14 silver badges27 bronze badges

6 Comments

inzero Over a year ago

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.

Gokhan Atil Over a year ago

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)?

inzero Over a year ago

Yes. the query used in my function always return one value. Sorry for asking these very basic things. I am new to snowflake.

Gokhan Atil Over a year ago

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.

inzero Over a year ago

Okay. I cannot use procedures because i want to use the result of this function in another query.

|

Somdeb Mukherjee · Accepted Answer · 2020-06-16 15:01:35Z

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

Ammar · Accepted Answer · 2023-10-06 05:04:43Z

0

You can use a trick similar to this.

create or replace function Test222 ( p1 int, p2 int ) returns Table ( v1 varchar ) language SQL as

'
select \'AA\'  

union
select \'BB\'
where p1=1

union
select \'CC \'
where p2=1


'

answered Oct 6, 2023 at 5:04

Ammar

1

Collectives™ on Stack Overflow

How to write dynamic queries in snowflake user defined functions

4 Answers 4

Comments

6 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

6 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related