How to Create UDFs in Redshift

Galaxy Glossary

How do I create and use UDFs in Amazon Redshift?

Amazon Redshift UDFs let you encapsulate reusable SQL or Python logic as callable database functions.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What is a UDF in Redshift?

A user-defined function (UDF) is custom code—written in SQL or Python—that you register once and reuse in any query, view, or dashboard. UDFs act like built-in functions but can include complex business rules that standard SQL lacks.

When should I use a UDF?

Use UDFs to centralize complicated CASE expressions, currency conversions, or machine-learning scores. Keeping logic in one place reduces copy-paste errors and speeds up query authoring.

How do I create a SQL UDF?

Write the function body in standard Redshift SQL, define input parameters and a return type, and execute CREATE FUNCTION.

Syntax breakdown

CREATE [ OR REPLACE ] FUNCTION schema_name.fn_name(param_name param_type, ...) RETURNS return_type IMMUTABLE | STABLE | VOLATILE AS $$ -- SQL statements $$ LANGUAGE SQL;

Choose IMMUTABLE for deterministic logic like tax rates, STABLE for read-only queries, and VOLATILE for non-deterministic functions.

How do I create a Python UDF?

Python UDFs run in AWS Lambda-style containers. Add the plpythonu language flag and write standard Python 3 code.

CREATE OR REPLACE FUNCTION public.format_currency(numeric) RETURNS varchar IMMUTABLE AS $$ import locale locale.setlocale(locale.LC_ALL, 'en_US.UTF-8') return locale.currency(args[0], grouping=True) $$ LANGUAGE plpythonu;

Pack extra libraries into a ZIP and reference it with library_path if needed.

How do I call a UDF?

Invoke it like any other function: SELECT schema_name.fn_name(arg1, arg2); Use it in projections, WHERE clauses, GROUP BY, or ORDER BY.

How do I replace or drop a UDF?

Update code with CREATE OR REPLACE FUNCTION. Remove it with DROP FUNCTION schema_name.fn_name(arg_type, ...);—be sure to list parameter types.

Best practices for UDFs

Mark functions IMMUTABLE whenever possible for better caching.
Store UDFs in a dedicated utils schema and grant EXECUTE to analysts.
Version critical UDFs by suffixing names (calc_margin_v2) to avoid breaking dashboards.

Common pitfalls

IMMUTABLE functions that query tables will silently return stale data. Python UDFs can slow queries if called per row—try set-based SQL UDFs first.

Why How to Create UDFs in Redshift is important

How to Create UDFs in Redshift Example Usage


-- Use the margin function in a report
SELECT o.id AS order_id,
       utils.calc_order_margin(o.id) AS gross_margin
FROM Orders o
ORDER BY gross_margin DESC;

How to Create UDFs in Redshift Syntax


-- SQL UDF
CREATE OR REPLACE FUNCTION utils.calc_order_margin(order_id integer)
RETURNS numeric(10,2)
IMMUTABLE
AS $$
    SELECT (oi.quantity*p.price - o.total_amount)::numeric(10,2)
    FROM Orders o
    JOIN OrderItems oi USING(order_id)
    JOIN Products p USING(product_id)
    WHERE o.id = order_id
$$ LANGUAGE SQL;

-- Python UDF
CREATE OR REPLACE FUNCTION utils.anonymize_email(text)
RETURNS text
IMMUTABLE
AS $$
    import hashlib
    return hashlib.sha256(args[0].encode()).hexdigest()
$$ LANGUAGE plpythonu;

Common Mistakes

Treating a non-deterministic function (e.g., using NOW()) as IMMUTABLE. Redshift may cache results, returning the same timestamp for every call. Declare such UDFs VOLATILE to force execution each time.
Calling Python UDFs row-by-row in large fact tables. Each call spins up an interpreter, causing severe latency. Rewrite logic as set-based SQL or aggregate before invoking the UDF.