CREATE FUNCTION

An overwiew of how Greenplum's user-defined functions are translated to Snowflake.

Description

Greenplum's procedural language allows users to create user-defined functions using SQL statements, functions, and operators. There are no Stored Procedures in the Greenplum environment, but since the procedural language can be used in Functions, this type of object is used instead.

Snowflake's user-defined functions, on the other hand, do not allow procedural language. So when SnowConvert finds a Greenplum function using the procedural language, it will convert it to a Stored Procedure. There are also Greenplum functions that are instead written using the SQL standard language; in this scenario, SnowConvert will also generate functions in Snowflake.

Functional Equivalence

There is, however, an important difference to take into account during the translation of these object types. In both Greenplum and Snowflake, functions can be called within queries like SELECT statements, but Stored Procedures cannot be used like this. So, when SnowConvert translates a Greenplum function to a Stored Procedure in Snowflake, there is a functionality loss regarding the way the object can be called. SnowConvert will add a comment to the Stored Procedures definition to warn users about this functional difference.

Grammar Syntax

This is the overview of the CREATE FUNCTION grammar.

CREATE [OR REPLACE] FUNCTION <name>    
    ( [ [<argmode>] [<argname>] <argtype> [ { DEFAULT | = } <default_expr> ] [, ...] ] )
      [ RETURNS <rettype> 
        | RETURNS TABLE ( <column_name> <column_type> [, ...] ) ]
    { LANGUAGE <langname>
    | WINDOW
    | IMMUTABLE | STABLE | VOLATILE | [NOT] LEAKPROOF
    | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
    | NO SQL | CONTAINS SQL | READS SQL DATA | MODIFIES SQL
    | [EXTERNAL] SECURITY INVOKER | [EXTERNAL] SECURITY DEFINER
    | EXECUTE ON { ANY | MASTER | ALL SEGMENTS | INITPLAN }
    | COST <execution_cost>
    | SET <configuration_parameter> { TO <value> | = <value> | FROM CURRENT }
    | AS '<definition>'
    | AS '<obj_file>', '<link_symbol>' } ...
    [ WITH ({ DESCRIBE = describe_function
           } [, ...] ) ]

Click here to go to the Greenplum specification for this syntax. Navigate to the following pages to get more details about the translation spec for CREATE FUNCTION.

Navigate to the following pages to get more details about the translations of Greenplum functions to Snowflake.

1. When Language is SQL.

2. When Language is PLPGSQL.