Output parameter
General Description
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.
Since functions are being transformed into procedures, the transformation reference for PL/SQL also applies here.
Single out parameter
Greenplum
CREATE OR REPLACE FUNCTION functionSingleOutputParameter(OUT Message VARCHAR(100))
AS $$
BEGIN
Message := 'Assignment value. Thanks!';
END;
$$
LANGUAGE PLPGSQL;
SELECT procedureLabelSingle();
CREATE OR REPLACE FUNCTION functionSQLSingleOutputParameter(param int, out min_len int)
AS $$
SELECT min(col1)
FROM table1
WHERE col2 > param
$$
LANGUAGE SQL;
SELECT * FROM functionSQLSingleOutputParameter(0);
Snowflake Scripting
/*** MSC-WARNING - MSC-GP0002 - NON-RETURNING FUNCTION TRANSLATED TO STORED PROCEDURE ***/
CREATE OR REPLACE PROCEDURE PUBLIC.functionSingleOutputParameter (
/*** MSC-ERROR - MSCEWI1078 - OUTPUT PARAMETERS ARE NOT SUPPORTED BY SNOWFLAKE SCRIPTING ***/
MESSAGE STRING)
RETURNS VARIANT
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
BEGIN
Message := 'Assignment value. Thanks';
RETURN Message;
END;
$$;
CALL functionSingleOutputParameter('');
/*** MSC-WARNING - MSC-GP0003 - RETURNING FUNCTION TRANSLATED TO STORED PROCEDURE ***/
CREATE OR REPLACE PROCEDURE PUBLIC.functionSQLSingleOutputParameter (
PARAM INT,
MIN_LEN INT /*** MSC-WARNING - MSCEWI1083 - OUTPUT PARAMETERS ARE NOT SUPPORTED BUT FUNCTIONALITY IS BEING EMULATED ***/)
RETURNS NUMBER(38, 18)
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
BEGIN
SELECT
min(col1)
INTO
:MIN_LEN
FROM
table1
WHERE
col2 > :PARAM;
RETURN MIN_LEN;
END;
$$;
call functionSQLSingleOutputParameter(0,0);
Multiple out parameter
Greenplum
CREATE OR REPLACE FUNCTION functionMultipleOutputParameter(OUT Message VARCHAR(100), OUT Message2 VARCHAR(100))
AS $$
BEGIN
Message := 'Assignment value. Thanks!';
Message2 := 'Assignment value2. Thanks';
END;
$$
LANGUAGE PLPGSQL;
SELECT functionMultipleOutputParameter();
CREATE OR REPLACE FUNCTION functionSQLMultipleOutputParameter(param int, out min_len int, out max_len int)
AS $$
SELECT min(col1), max(col1)
FROM table1
WHERE col2 > param
$$
LANGUAGE SQL;
SELECT * FROM functionSQLMultipleOutputParameter(0);
Snowflake Scripting
/*** MSC-WARNING - MSC-GP0002 - NON-RETURNING FUNCTION TRANSLATED TO STORED PROCEDURE ***/
CREATE OR REPLACE PROCEDURE PUBLIC.functionMultipleOutputParameter (
/*** MSC-ERROR - MSCEWI1078 - OUTPUT PARAMETERS ARE NOT SUPPORTED BY SNOWFLAKE SCRIPTING ***/
MESSAGE STRING,
/*** MSC-ERROR - MSCEWI1078 - OUTPUT PARAMETERS ARE NOT SUPPORTED BY SNOWFLAKE SCRIPTING ***/
MESSAGE2 STRING)
RETURNS VARIANT
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
BEGIN
Message := 'Assignment value. Thanks';
Message2 := 'Assignment value2. Thanks';
RETURN OBJECT_CONSTRUCT('Message', :Message, 'Message2', :Message2);
END;
$$;
CALL functionMultipleOutputParameter('','');
/*** MSC-WARNING - MSC-GP0003 - RETURNING FUNCTION TRANSLATED TO STORED PROCEDURE ***/
CREATE OR REPLACE PROCEDURE functionSQLMultipleOutputParameter(
param NUMBER(38, 18),
min_len NUMBER(38, 18), /*** MSC-WARNING - MSCEWI1083 - OUTPUT PARAMETERS ARE NOT SUPPORTED BUT FUNCTIONALITY IS BEING EMULATED ***/
max_len NUMBER(38, 18))
RETURNS VARIANT
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
BEGIN
select min(col1),
max(col1)
into min_len, max_len
from table1
WHERE col2 > :param
;
/*** MSC-WARNING - MSCEWI1082 - AUTO-GENERATED CODE IN ORDER TO SUPPORT OUT PARAMETERS IN SNOWFLAKE SCRIPTING ***/
RETURN OBJECT_CONSTRUCT('min_len', :min_len, 'max_len', :max_len);
END;
$$
call functionSQLMultipleOutputParameter(0,0,0);
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.
Related EWIs
MSC-GP0002: Non-returning Function translated to Stored Procedure.
MSC-GP0003: Non-returning Function translated to Stored Procedure.
MSCEWI1082: Auto-Generated code in order to support output parameters in Snowflake Scripting
MSCEWI1083: Output parameters are not supported but their functionality is being emulated
Last updated
Was this helpful?