Creating SQL and PL/pgSQL Functions

PostgreSQL supports user-defined functions written in SQL, PL/pgSQL (a procedural language with control flow), PL/Python, PL/Perl, and others. SQL functions are the simplest form — just a named SQL query. PL/pgSQL functions support variables, loops, conditionals, exception handling, and cursors. Functions are called like built-in functions and can return scalars, records, sets of records (SETOF or TABLE(...)), or void. The LANGUAGE clause specifies the implementation language, and RETURNS specifies the return type. Functions marked IMMUTABLE (result depends only on arguments, no side effects), STABLE (no side effects, may read tables), or VOLATILE (default, can do anything) affect how the query optimizer can use them.

Example

-- Simple SQL function
CREATE OR REPLACE FUNCTION calculate_tax(price NUMERIC, rate NUMERIC DEFAULT 0.08)
RETURNS NUMERIC AS $$
  SELECT ROUND(price * rate, 2);
$$ LANGUAGE sql IMMUTABLE;

SELECT calculate_tax(99.99);         -- 8.00
SELECT calculate_tax(99.99, 0.10);   -- 10.00

-- PL/pgSQL function with control flow
CREATE OR REPLACE FUNCTION get_user_tier(user_id INTEGER)
RETURNS TEXT AS $$
DECLARE
  lifetime_spend NUMERIC;
  tier TEXT;
BEGIN
  SELECT COALESCE(SUM(total), 0)
    INTO lifetime_spend
    FROM orders
   WHERE orders.user_id = get_user_tier.user_id
     AND status = 'completed';

  tier := CASE
    WHEN lifetime_spend >= 5000 THEN 'platinum'
    WHEN lifetime_spend >= 1000 THEN 'gold'
    WHEN lifetime_spend >= 100  THEN 'silver'
    ELSE 'bronze'
  END;

  RETURN tier;
END;
$$ LANGUAGE plpgsql STABLE;

SELECT id, name, get_user_tier(id) AS tier FROM users LIMIT 10;

HTML

Full Editor

Related Resources

PostgreSQL Reference

Complete tag & property list

PostgreSQL How-To Guides

Step-by-step practical guides

PostgreSQL Exercises

Practice what you've learned