2

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.

Liumx31
  • 1,190
  • 1
  • 16
  • 33

2 Answers2

1

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

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
0

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

53epo
  • 784
  • 5
  • 7