Lessons › SQL › Deep Dive: Database Admin & ETL

SQL lesson

Stored procedures vs functions

Stored procedures run a sequence of actions - updates, deletions, branching logic - but don't return a value you can slot directly into a query.

Example

Choosing Between Stored Procedures and Functions in Retail SQL

CALL mark_orders_shipped('2024-03-03');

SELECT order_id, revenue, revenue_tier(revenue) AS tier
FROM orders

Result:

Step by step

1. CREATE PROCEDURE defines a reusable set of steps you can run with CALL.

   CREATE OR REPLACE PROCEDURE mark_orders_shipped(cutoff_date DATE)
   LANGUAGE plpgsql
   AS $$
   BEGIN

2. UPDATE modifies existing rows in a table.

   CREATE OR REPLACE PROCEDURE mark_orders_shipped(cutoff_date DATE)
   LANGUAGE plpgsql
   AS $$
   BEGIN
   UPDATE orders
     SET status = 'shipped'

3. WHERE keeps only the rows that match your condition.

   CREATE OR REPLACE PROCEDURE mark_orders_shipped(cutoff_date DATE)
   LANGUAGE plpgsql
   AS $$
   BEGIN
   UPDATE orders
     SET status = 'shipped'
   WHERE status = 'pending'
       AND order_date <= cutoff_date;
   END;
   $$;

Practice

Try each one in your head first, then open the answer.

CREATE OR REPLACE FUNCTION double_value(n INT) RETURNS INT AS $$ SELECT n * 2 $$ LANGUAGE SQL

CREATE OR REPLACE FUNCTION double_value(n INT) RETURNS INT AS $$ SELECT n * 2 $$ LANGUAGE SQL

CREATE OR REPLACE FUNCTION double_value(n INT) RETURNS INT AS $$ SELECT n * 2 $$ LANGUAGE SQL

CREATE OR REPLACE FUNCTION double_value(n INT) RETURNS INT AS $$ SELECT n * 2 $$ LANGUAGE SQL

CREATE OR REPLACE FUNCTION double_value(n INT) RETURNS INT AS $$ SELECT n * 2 $$ LANGUAGE SQL