How to Create Stored Procedures in Amazon Redshift

How do I create and run stored procedures in Amazon Redshift?

Stored procedures in Redshift let you package SQL logic in reusable, parameter-driven routines executed with a single CALL statement.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What is a stored procedure in Redshift?

A stored procedure is a named set of SQL statements written in PL/pgSQL or Python that runs on the cluster. It supports parameters, control-flow, and transaction management, enabling complex, reusable business logic.

When should I use a stored procedure?

Use procedures to encapsulate multi-step ETL, enforce data quality rules, or implement auditing wrapped in a single, version-controlled unit. They reduce repetitive code and centralize business rules.

How do I create a stored procedure?

Define the procedure with CREATE PROCEDURE, specify input parameters, language, and body. Use $$ delimiters for the code block.

CREATE OR REPLACE PROCEDURE add_order( p_customer_id INT, p_product_id INT, p_qty INT) LANGUAGE plpgsql AS $$ DECLARE v_price NUMERIC; BEGIN -- Get current product price SELECT price INTO v_price FROM Products WHERE id = p_product_id; -- Insert order header INSERT INTO Orders(customer_id, order_date, total_amount) VALUES (p_customer_id, CURRENT_DATE, v_price * p_qty) RETURNING id INTO STRICT _order_id; -- Insert order item INSERT INTO OrderItems(order_id, product_id, quantity) VALUES (_order_id, p_product_id, p_qty); END; $$;

How do I execute a stored procedure?

Run the procedure with CALL and supply arguments in order.

CALL add_order(42, 7, 3);

Can I pass named parameters?

No. Redshift supports positional parameters only. Ensure argument order matches the definition.

How do I modify or drop a procedure?

Use CREATE OR REPLACE to update logic without dropping privileges. Remove a procedure permanently with DROP PROCEDURE name(arg_types).

DROP PROCEDURE add_order(INT, INT, INT);

What are best practices?

Use explicit transaction control (BEGIN, COMMIT) for multi-step DML. Validate inputs at the top of the routine. Keep procedures idempotent where possible and version them in source control.

What are common mistakes?

Avoid dynamic SQL when static statements suffice; it slows compilation and complicates privileges. Always qualify object names with schema to prevent search-path surprises.

Why How to Create Stored Procedures in Amazon Redshift is important

How to Create Stored Procedures in Amazon Redshift Example Usage


-- Summarize a customer’s total spend
CREATE OR REPLACE PROCEDURE customer_spend(p_customer_id INT)
LANGUAGE plpgsql AS $$
BEGIN
    SELECT c.name,
           SUM(o.total_amount) AS lifetime_value
    FROM Customers c
    JOIN Orders o ON o.customer_id = c.id
    WHERE c.id = p_customer_id
    GROUP BY c.name;
END;
$$;

CALL customer_spend(42);

How to Create Stored Procedures in Amazon Redshift Syntax


CREATE [OR REPLACE] PROCEDURE procedure_name (
    [arg_name arg_type [, ...]] )
[LANGUAGE plpgsql | plpythonu]
AS $$
DECLARE
    -- variable declarations
BEGIN
    -- ecommerce example
    -- 1. calculate order total from Products
    -- 2. insert into Orders and OrderItems
END;
$$

-- Execute
CALL procedure_name(arg_value [, ...]);

Common Mistakes

Using SELECT instead of PERFORM inside PL/pgSQL when the result is unused. Use PERFORM to suppress ‘query has no destination’ notices.
Omitting argument types in DROP PROCEDURE. Redshift requires the exact parameter signature; use DROP PROCEDURE name(type, …).