CREATE FUNCTION

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

Description

This command defines a user-defined function (UDF) within the database. These functions encapsulate reusable logic that can be invoked within SQL queries.

Grammar Syntax

The following is the SQL syntax to create a view in Amazon Redshift. Click here to here to go to Redshifts specification for this syntax.

CREATE [ OR REPLACE ] FUNCTION f_function_name
( { [py_arg_name  py_arg_data_type |
sql_arg_data_type } [ , ... ] ] )
RETURNS data_type
{ VOLATILE | STABLE | IMMUTABLE }
AS $$
  { python_program | SELECT_clause }
$$ LANGUAGE { plpythonu | sql }               

SQL Language

Volatility category

In Snowflake, VOLATILE and IMMUTABLE function volatility are functionally equivalent. Given that STABLE is inherently transformed to the default VOLATILE behavior, explicit use of STABLE will be delete.

Input Code:

Redshift

IN -> Redshift_01.sql

CREATE OR REPLACE FUNCTION get_sale(INTEGER)
RETURNS FLOAT
STABLE
AS $$
SELECT price FROM sales where id = $1
$$ LANGUAGE SQL;

Output Code:

Snowflake

OUT -> Redshift_01.sql

CREATE OR REPLACE FUNCTION get_sale (SC_ARG1 INTEGER)
RETURNS FLOAT
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "07/11/2025",  "domain": "no-domain-provided" }}'
AS $$
SELECT price FROM
sales
where id = SC_ARG1
$$
;

Python Language

Within the SnowConvert scope, the Python language for CREATE FUNCTION statements is not supported. Consequently, the language plpythonu will be flagged with an EWI (SSC-EWI-0073), and its body could appear with parsing errors.

Input Code:

Redshift

IN -> Redshift_02.sql

create function f_py_greater (a float, b float)
  returns float
stable
as $$
  if a > b:
    return a
  return b
$$ language plpythonu;

Output Code:

Snowflake

OUT -> Redshift_02.sql

create function f_py_greater (a float, b float)
returns float
language plpythonu !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'LANGUAGE PLPythonU' NODE ***/!!!
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "07/11/2025",  "domain": "no-domain-provided" }}'
as $$
-- ** SSC-EWI-0001 - UNRECOGNIZED TOKEN ON LINE '5' COLUMN '3' OF THE SOURCE CODE STARTING AT 'if'. EXPECTED 'STATEMENT' GRAMMAR. LAST MATCHING TOKEN WAS 'if' ON LINE '5' COLUMN '3'. **
--  if a > b:
--    return a
--  return b
$$
;

Related EWIs

There are no known EWIs.