Greenplum UDFs with output parameters are being transformed to Snowflake Stored Procedures, to maintain functional equivalence, due to Snowflake UDFs having some limitations executing DML (Data Manipulation Language) statements.
CREATE OR REPLACEFUNCTIONfunctionSingleOutputParameter(OUTMessageVARCHAR(100))AS $$BEGINMessage :='Assignment value. Thanks!';END;$$ LANGUAGE PLPGSQL;SELECT procedureLabelSingle();CREATE OR REPLACEFUNCTIONfunctionSQLSingleOutputParameter(paramint, out min_len int)AS $$SELECTmin(col1)FROM table1 WHERE col2 >param $$LANGUAGESQL;SELECT*FROM functionSQLSingleOutputParameter(0);
Inside a procedure, the returned object can be easily deconstructed by using the following statements right after performing the procedure call:
LET call_results VARIANT := (SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())));
var1 := GET(:call_results, 'Message');
var2 := GET(:call_results, 'Message2');
Known Issues
1. Out Parameters
Snowflake Scripting does not support OUT/INOUT parameters therefore these parameters are returned at the end of the procedure's body.
2. REFCURSOR Type not supported
In Snowflake, you can use stored procedures to execute SQL statements and return result sets, but the result sets are not returned as REFCURSOR objects. Instead, Snowflake supports returning result sets as tables, which can be queried and manipulated using SQL.
3. Missing name for the parameter
In Snowflake, all function parameters must have names, even if they also have data types.
4. Functions with parameterized queries are migrated to stored procedures in Snowflake
One of the main limitations of functions in Snowflake is that they do not allow the execution of parameterized queries within the function. That is why the use of stored procedures is made to emulate this functionality.