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

Query

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

Result

Snowflake Scripting

Query

/*** 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);

Result

Multiple out parameter

Greenplum

Query

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

Result

Snowflake Scripting

Query

/*** 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);

Result

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.

Related EWIs

1. MSC-GP0002: Non-returning Function translated to Stored Procedure.

2. MSC-GP0003: Non-returning Function translated to Stored Procedure.

3. MSCEWI1082: Auto-Generated code in order to support output parameters in Snowflake Scripting

4. MSCEWI1083: Output parameters are not supported but their functionality is being emulated