CODEWITHSUNDEEP
sqlfunctiondynamicsnowflake-cloud-data-platform
inzero
inzero

Reputation: 75

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_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

Upvotes: 2

Views: 12398

Answers (4)

Ammar
Ammar

Reputation: 1

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


'

Upvotes: 0

Somdeb Mukherjee
Somdeb Mukherjee

Reputation: 178

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

Upvotes: 0

MMV
MMV

Reputation: 980

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

Upvotes: 2

Gokhan Atil
Gokhan Atil

Reputation: 10039

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' );

Upvotes: 0

Related Questions