User-Defined Functions

Learning Focus

Use this lesson to write PostgreSQL CREATE FUNCTION routines, understand return types (scalar vs set), and choose attributes like IMMUTABLE/STABLE/VOLATILE.

Concept Overview

A PostgreSQL function is a database routine that returns a value.

Functions can be:

• SQL functions: small, declarative wrappers
• PL/pgSQL functions: procedural logic with variables, loops, and exceptions

Functions can return:

• a single value (RETURNS integer)
• a table-like set (RETURNS TABLE (...))
• a set of rows (RETURNS SETOF ...)

Why is it important?

• Reusability: avoid duplicating logic across queries
• Consistency: shared computation rules
• Composability: functions can be used inside SELECT, WHERE, JOIN, and ORDER BY
• Performance: correct volatility helps the planner make better decisions

Where does it fit?

Functions are used in:

• query building (computed columns)
• data validation helpers
• reporting and analytics
• triggers (trigger functions)

---

Syntax & Rules

Core Syntax

SQL function:

CREATE OR REPLACE FUNCTION fn_name(arg_name arg_type)
RETURNS return_type
LANGUAGE sql
AS $$
  SELECT ...;
$$;

PL/pgSQL function:

CREATE OR REPLACE FUNCTION fn_name(arg_name arg_type)
RETURNS return_type
LANGUAGE plpgsql
AS $$
DECLARE
  v_var integer;
BEGIN
  -- statements
  RETURN v_var;
END;
$$;

Drop a function (signature required):

DROP FUNCTION IF EXISTS fn_name(arg_type, arg_type);

Volatility Categories

Attribute	Meaning	Examples
IMMUTABLE	always same output for same input	pure math, string normalization
STABLE	same output within a statement	lookup tables, current_setting(...)
VOLATILE	can change within a statement	random(), now()

Available Options / Parameters

Feature	Example	Meaning / Notes
RETURNS TABLE	RETURNS TABLE (id bigint, total numeric)	set-returning function with named columns
SECURITY DEFINER	SECURITY DEFINER	runs with function owner's privileges (use carefully)
SET search_path	SET search_path = public	harden security for definer functions
Strictness	STRICT	returns NULL if any argument is NULL

Key Rules and Considerations

• A function is called with SELECT fn_name(...).
• Drops/overloads require signatures (name + arg types).
• Volatility matters: declaring a function IMMUTABLE when it isn't can produce wrong results.
• SECURITY DEFINER is powerful and dangerous; use only with a deliberate privilege model.

---

Step-by-Step Examples

Example 1: A Simple SQL Function (Beginner)

CREATE OR REPLACE FUNCTION normalize_email(p_email text)
RETURNS text
LANGUAGE sql
IMMUTABLE
AS $$
  SELECT lower(trim(p_email));
$$;

SELECT normalize_email('  Alice@Example.com  ') AS normalized;

Expected output:

    normalized
-------------------
 alice@example.com
(1 row)

Explanation:

• Declaring IMMUTABLE is correct here because the function is pure.

---

Example 2: A PL/pgSQL Function With Variables (Intermediate)

CREATE OR REPLACE FUNCTION add_tax(p_amount numeric, p_rate numeric)
RETURNS numeric
LANGUAGE plpgsql
IMMUTABLE
AS $$
DECLARE
  v_total numeric;
BEGIN
  v_total := p_amount * (1 + p_rate);
  RETURN v_total;
END;
$$;

SELECT add_tax(100.00, 0.07) AS total;

Expected output:

 total
--------
 107.00
(1 row)

Explanation:

• PL/pgSQL adds procedural structure even for simple computations.

---

Example 3: Set-Returning Function (RETURNS TABLE) (Advanced)

DROP TABLE IF EXISTS orders;

CREATE TABLE orders (
  order_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL,
  amount numeric(12,2) NOT NULL CHECK (amount >= 0)
);

INSERT INTO orders (customer_id, amount) VALUES
  (1, 10.00),
  (1, 25.00),
  (2, 50.00);

CREATE OR REPLACE FUNCTION customer_totals()
RETURNS TABLE (customer_id bigint, total numeric)
LANGUAGE sql
STABLE
AS $$
  SELECT customer_id, SUM(amount)
  FROM orders
  GROUP BY customer_id
  ORDER BY customer_id;
$$;

SELECT * FROM customer_totals();

Expected output:

 customer_id | total
-------------+-------
 1           | 35.00
 2           | 50.00
(2 rows)

Explanation:

• RETURNS TABLE makes the function feel like a parameterless view.
• STABLE is a good default for functions that read tables.

---

Practical Use Cases

1) Standardize Normalization Rules

Context: ensure emails are normalized the same way everywhere.

SELECT normalize_email(email) FROM users;

2) Reusable Reporting Helpers

Context: expose computed rollups.

SELECT * FROM customer_totals();

3) Trigger Support

Context: PL/pgSQL functions are commonly used by triggers.

-- Trigger functions are written as RETURNS trigger.

---

Common Mistakes & Troubleshooting

Mistake 1: Wrong Volatility Declaration

Wrong:

-- now() changes, so this is not IMMUTABLE
CREATE FUNCTION bad() RETURNS timestamptz LANGUAGE sql IMMUTABLE AS $$ SELECT now(); $$;

What happens: the planner may cache results in ways that surprise you.

Fix: use VOLATILE (default) for time/random.

---

Mistake 2: Forgetting Function Signatures in DROP

Wrong:

DROP FUNCTION normalize_email;

Fix:

DROP FUNCTION normalize_email(text);

---

Mistake 3: Unsafe SECURITY DEFINER

What happens: privilege escalation risk.

Fix: avoid unless necessary; if used, lock down search_path and grants.

Debugging tips:

1. List functions with \df+ and check argument types.
2. Use \sf function_name in psql to view function source.
3. Start with SQL functions for simple wrappers.
4. Test with representative input, including NULLs.

---

Best Practices

• ✅ Use SQL functions for simple logic; ❌ write everything in PL/pgSQL by default.
• ✅ Declare correct volatility; ❌ mark functions IMMUTABLE without proof.
• ✅ Keep functions small and composable; ❌ build giant "do everything" functions.
• ✅ Use RETURNS TABLE for view-like helpers; ❌ return anonymous record types.
• ✅ Treat SECURITY DEFINER as privileged code; ❌ use it casually.

---

Hands-On Practice

Exercise 1 (Easy): Create a normalization function

Task: Create practice_trim_lower(text) that trims and lowercases a string.

-- Your SQL here

Solution:

CREATE OR REPLACE FUNCTION practice_trim_lower(p text)
RETURNS text
LANGUAGE sql
IMMUTABLE
AS $$
  SELECT lower(trim(p));
$$;

---

Exercise 2 (Medium): Use the function in a query

Task: Call practice_trim_lower(' Hi ').

-- Your SQL here

Solution:

SELECT practice_trim_lower('  Hi  ') AS v;

---

Exercise 3 (Advanced): Create a set-returning function

Task: Create a function that returns (n, n*n) for n from 1 to 3.

-- Your SQL here

Solution:

CREATE OR REPLACE FUNCTION practice_squares()
RETURNS TABLE (n integer, square integer)
LANGUAGE sql
IMMUTABLE
AS $$
  SELECT x, x*x
  FROM generate_series(1, 3) AS x;
$$;

---

Connection to Other Concepts

Concept	Why it matters
Stored procedures	procedures are called; functions return values
Triggers	trigger logic is written in functions
Indexes	expression indexes often depend on stable/immutable functions
Security	SECURITY DEFINER affects privilege boundaries
Date/time functions	volatility matters for time-based functions

---

Visual Learning Diagram

flowchart LR
  A[SELECT query] --> B[call function]
  B --> C[return value\n(scalar or set)]
  C --> D[used in WHERE/JOIN/SELECT]
  B --> E[volatility\nIMMUTABLE/STABLE/VOLATILE]

  classDef allNodes fill:#3e3e3e,stroke:#ffffff,stroke-width:2px,color:#f5f5f5
  classDef highlight fill:#3e3e3e,stroke:#ffffff,stroke-width:4px,color:#f5f5f5
  class A,B,C,D,E allNodes
  class B highlight

---

Common Pitfalls

Pitfall	Consequence	Prevention
Wrong volatility	wrong plans/results	declare volatility accurately
Missing signature in drops	cannot drop	always specify arg types
Overusing PL/pgSQL	more complexity	prefer SQL functions for simple logic
Returning untyped records	awkward consumers	use RETURNS TABLE
Unsafe SECURITY DEFINER	security risk	avoid or harden carefully

---

Quick Reference

CREATE OR REPLACE FUNCTION f(a int) RETURNS int LANGUAGE sql AS $$ SELECT a+1; $$
DROP FUNCTION IF EXISTS f(int)
SELECT f(1)
CREATE OR REPLACE FUNCTION g() RETURNS TABLE (x int) LANGUAGE sql AS $$ SELECT 1; $$
\df+

What's Next

• Previous: Stored Procedures Basics - Use CALL for procedure-style workflows.
• Next: Control Flow and Error Handling - Add branching, loops, and exceptions.
• Module Overview - Return to Stored Procedures and Functions overview.