CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
Liumx31
Liumx31

Reputation: 1210

Parameterization in Snowflake UDF

I'm currently trying to refactor some SQL logic in Snowflake to improve readability and reduce repeated code by using UDF's.

Here is the UDF I'm trying to create:

create or replace function myfunc(var1 varchar, var2 varchar)
returns table (result int)
as 
$$
select var1 from table1
where var2 = 1
$$;

select * from table(myfunc(column1, column2));

I want var1 and var2 to be two column names in table1 but not sure how to do that in SQL/Snowflake UDF.

Upvotes: 2

Views: 1318

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 176124

Snowflake allows to use IDENTIFIER and variables to achieve similar effect:

 SET (VAR1, VAR2) = ('my_col1', 'my_col2');
 
 SELECT IDENTIFIER($VAR1)
 FROM table_name
 WHERE IDENTIFIER($VAR2) = 1;

However this kind of syntax cannot be wrapped with UDTF. To parametrize column list/table name dynamic SQL(stored procedure) could be used.

Sidenote: The pattern used in question:

select * from table(myfunc(column1, column2));

is called Polymorphic Table Function(PTF) and it is defined in ISO/IEC TR 19075-7:2017 Information technology — Database languages — SQL Technical Reports — Part 7: Polymorphic table functions in SQL

It allows to shape the result's structure during runtime and provides greater flexibility. An example could be SELECT * EXCEPT

Upvotes: 1

53epo
53epo

Reputation: 909

According to this it's not possible in a UDF, and there are no examples in the Snowflake UDF docs beyond plain (i.e. non-dynamic) SQL.

But it is possible as a stored procedure if that's an option for you. The Snowflake docs have examples of the syntax (and tips/warnings about SQL injection).

Upvotes: 0

Related Questions