Functions (CREATE FUNCTION)

Encapsulate reusable SQL logic in user-defined functions.

Overview

User-defined functions (UDFs) let you package reusable logic inside the database. A function takes parameters, performs computation, and returns a value (scalar, table, or set). Functions can be written in SQL, PL/pgSQL, or other languages (Python, Perl, etc.). SQL functions are simplest just a SELECT wrapped in a function. PL/pgSQL functions support variables, loops, conditionals, and exception handling. Functions are called like built-in functions: SELECT my_function(arg).

Key Points

•CREATE FUNCTION name(params) RETURNS type LANGUAGE sql/plpgsql AS $$...$$
•SQL functions: simple wrapper around SQL statements
•PL/pgSQL: variables, IF/ELSE, loops, exception handling
•RETURNS TABLE (...) returns multiple rows
•IMMUTABLE/STABLE/VOLATILE volatility hints for planner optimisation
•DROP FUNCTION name(arg_types) remove a function

Examples

SQL and PL/pgSQL functions

-- Simple SQL function
CREATE OR REPLACE FUNCTION add_tax(price NUMERIC, rate NUMERIC DEFAULT 0.1)
RETURNS NUMERIC LANGUAGE sql IMMUTABLE AS $$
  SELECT price * (1 + rate);
$$;

SELECT add_tax(100);         -- 110
SELECT add_tax(100, 0.2);    -- 120

-- PL/pgSQL with logic
CREATE OR REPLACE FUNCTION get_discount(price NUMERIC)
RETURNS NUMERIC LANGUAGE plpgsql AS $$
BEGIN
  IF price > 100 THEN RETURN price * 0.9;
  ELSE RETURN price;
  END IF;
END;
$$;

Functions (CREATE FUNCTION)

Overview

Key Points

Examples

Exercises

Knowledge Check

What language is used for a function that needs IF/ELSE logic?