Snowflake schema and table reference in User Defined Function arguments

user_3158739 0
Viewed 284

I'm trying to create a User Defined Function (UDF) in Snowflake where the arguments can take in schema, table, and column values as variables so that the function can dynamically call different tables. I tried to do this and save the function but Snowflake threw an error saying that the schema name did not exist - it tried to look for the schema using the argument name rather than the variable that will be entered into the function arguments.

Here is an example of what I'm trying to do:

CREATE OR REPLACE FUNCTION "DB_NAME"."SCHEMA_NAME"."FUNCTION_NAME"(
    SCHEMA_VAR VARCHAR,
    TABLE_VAR VARCHAR,
    COLUMN_VAR VARCHAR)
RETURNS TABLE (RETURN_COL VARCHAR)
AS
$$
SELECT COLUMN_VAR FROM "DB_NAME".SCHEMA_VAR.TABLE_VAR
$$;
snowflake-cloud-data-platform sql
2 Answers

What you are describing is dynamic SQL which is only allowed from a stored procedure, not a function.

From normal SQL you can use a TABLE LITERAL to have dynamical named tables from the perspective of your code running the SQL, but then you can also just do string manipluation in your code, but this method makes it injection safe.

user_439920

Honestly, even if what you're trying to achieve was doable using a UDF, it's an overkill. Consider below

set (table_name, column_name) = ('mydb.myschema.mytable', 'mycolumn');

select $column_name
from identifier($table_name);
user_99471590
Related