Function vs procedure decision in SQL

Introduction

Functions and procedures help organize database tasks. Choosing the right one makes your work easier and clearer.

Use a function when you need to return a single value, like calculating a total price.

Use a procedure when you want to perform actions like updating multiple tables.

Use a function inside a query to get a value for each row.

Use a procedure to run a set of commands that do not return a value directly.

Use a function when you want to reuse a calculation in many places.

Syntax

SQL

CREATE FUNCTION function_name(parameters) RETURNS return_type AS $$
BEGIN
  -- function logic
  RETURN value;
END;
$$ LANGUAGE plpgsql;

CREATE PROCEDURE procedure_name(parameters) AS $$
BEGIN
  -- procedure logic
END;
$$ LANGUAGE plpgsql;

Functions always return a value.

Procedures do not return values but can return results via output parameters or affect the database.

Examples

This function returns a 10% discount on the price.

SQL

CREATE FUNCTION get_discount(price numeric) RETURNS numeric AS $$
BEGIN
  RETURN price * 0.9;
END;
$$ LANGUAGE plpgsql;

This procedure reduces the stock of a product by a given amount.

SQL

CREATE PROCEDURE update_stock(product_id int, amount int) AS $$
BEGIN
  UPDATE products SET stock = stock - amount WHERE id = product_id;
END;
$$ LANGUAGE plpgsql;

Sample Program

The function calculate_tax returns 7% tax of an amount. The procedure apply_tax uses this function to update the tax for an order.

SQL

CREATE FUNCTION calculate_tax(amount numeric) RETURNS numeric AS $$
BEGIN
  RETURN amount * 0.07;
END;
$$ LANGUAGE plpgsql;

CREATE PROCEDURE apply_tax(order_id int) AS $$
DECLARE
  order_amount numeric;
  tax_amount numeric;
BEGIN
  SELECT total INTO order_amount FROM orders WHERE id = order_id;
  tax_amount := calculate_tax(order_amount);
  UPDATE orders SET tax = tax_amount WHERE id = order_id;
END;
$$ LANGUAGE plpgsql;

-- Example usage:
-- SELECT calculate_tax(100);
-- CALL apply_tax(1);

Important Notes

Functions can be used in SELECT queries; procedures cannot.

Procedures can perform complex tasks like transactions and multiple updates.

Use functions for calculations and procedures for actions.

Summary

Functions return values and can be used in queries.

Procedures perform actions and do not return values directly.

Choose functions for calculations and procedures for tasks.