CREATE FUNCTION

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
STABLE
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "04/15/2025",  "domain": "test" }}'
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, any CREATE FUNCTION statement utilizing Python will be flagged with an EWI (SSC-EWI-0001 )indicator for parsing error.

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

-- ** SSC-EWI-0001 - UNRECOGNIZED TOKEN ON LINE '8' COLUMN '0' OF THE SOURCE CODE STARTING AT 'create'. EXPECTED 'Create Function Statement' GRAMMAR. LAST MATCHING TOKEN WAS 'create' ON LINE '8' COLUMN '0'. FAILED TOKEN WAS 'as' ON LINE '11' COLUMN '1'. **
--create function f_py_greater (a float, b float)
--  returns float
--stable
--as $$
--  if a > b:
--    return a
--  return b
--$$ language plpythonu;

Related EWIs

There are no known EWIs.