Introduction to Functions

Functions are reusable blocks of SQL code that perform specific tasks. PostgreSQL comes with hundreds of built-in functions for everything from string manipulation to date calculations. You can also create your own custom functions using PL/pgSQL, PostgreSQL's procedural language.

Built-in Functions

PostgreSQL provides functions for common operations:

-- String functions
SELECT UPPER('hello');           -- HELLO
SELECT LOWER('HELLO');           -- hello
SELECT LENGTH('PostgreSQL');     -- 10
SELECT TRIM('  hello  ');        -- hello
SELECT CONCAT('Hello', ' ', 'World');  -- Hello World

-- Date functions
SELECT NOW();                    -- Current timestamp
SELECT CURRENT_DATE;             -- Current date
SELECT EXTRACT(YEAR FROM NOW()); -- Current year
SELECT AGE('2000-01-01');        -- Interval from date to now

-- Math functions
SELECT ABS(-42);                 -- 42
SELECT ROUND(3.14159, 2);       -- 3.14
SELECT CEIL(4.1);               -- 5
SELECT FLOOR(4.9);              -- 4
SELECT POWER(2, 10);            -- 1024

Creating Custom Functions

You can create your own functions with PL/pgSQL:

CREATE OR REPLACE FUNCTION calculate_grade(gpa NUMERIC)
RETURNS TEXT AS $$
BEGIN
  IF gpa >= 3.7 THEN RETURN 'A';
  ELSIF gpa >= 3.0 THEN RETURN 'B';
  ELSIF gpa >= 2.0 THEN RETURN 'C';
  ELSIF gpa >= 1.0 THEN RETURN 'D';
  ELSE RETURN 'F';
  END IF;
END;
$$ LANGUAGE plpgsql;

-- Use it
SELECT first_name, calculate_grade(avg_gpa) AS grade
FROM student_gpa;

The function takes a GPA and returns a letter grade. The $$ delimiters mark the function body. LANGUAGE plpgsql tells PostgreSQL which language to use.

Functions with Parameters

Functions can have multiple parameters with default values:

CREATE OR REPLACE FUNCTION greet(
  name TEXT,
  greeting TEXT DEFAULT 'Hello'
)
RETURNS TEXT AS $$
BEGIN
  RETURN greeting || ', ' || name || '!';
END;
$$ LANGUAGE plpgsql;

-- Use with defaults
SELECT greet('Alice');           -- Hello, Alice!

-- Override the default
SELECT greet('Bob', 'Hi');       -- Hi, Bob!

Table-Returning Functions

Functions can return entire tables, which is useful for encapsulating complex queries:

CREATE OR REPLACE FUNCTION get_top_students(min_gpa NUMERIC)
RETURNS TABLE(first_name TEXT, last_name TEXT, gpa NUMERIC) AS $$
BEGIN
  RETURN QUERY
  SELECT s.first_name, s.last_name, sg.avg_gpa
  FROM students s
  JOIN student_grades sg ON s.id = sg.student_id
  WHERE sg.avg_gpa >= min_gpa
  ORDER BY sg.avg_gpa DESC;
END;
$$ LANGUAGE plpgsql;

-- Use it like a table
SELECT * FROM get_top_students(3.5);

Managing Functions

To view a function's definition:

\df+ calculate_grade

To list all functions:

SELECT routine_name, routine_type
FROM information_schema.routines
WHERE routine_schema = 'public';

To remove a function:

DROP FUNCTION calculate_grade(NUMERIC);

-- Or if it has parameters
DROP FUNCTION greet(TEXT, TEXT);