Functions

PostgreSQL offers built-in functions and the ability to create user-defined functions. Functions differ from stored procedures in that they return a value and can be used inside SQL statements (SELECT, WHERE, etc.).

Test Data Generation Functions

PostgreSQL offers functions that help us generate test data for testing purposes.

random() generates a random function between 0-1

repeat('text',5) repeats the word text 5 times horizontally

generate_series(1,5) generates 5 rows with values 1,2,3,4,5.

here is an example query

SELECT 'user' || trunc(random()*100000) || repeat('x',10) FROM generate_series(1,5);

Aggregate Functions

Used with GROUP BY to compute values across rows. See Window Functions for non-collapsing alternatives.

Function	Description
COUNT(*)	Number of rows
SUM(col)	Sum of values
AVG(col)	Average
MIN(col) / MAX(col)	Minimum / Maximum
STRING_AGG(col, ',')	Concatenate strings with delimiter
ARRAY_AGG(col)	Collect values into an array
BOOL_AND(col) / BOOL_OR(col)	Logical AND / OR across rows

String Functions

Function	Example	Result
LENGTH(s)	LENGTH('hello')	5
UPPER(s) / LOWER(s)	UPPER('hello')	'HELLO'
TRIM(s)	TRIM(' hi ')	'hi'
SUBSTRING(s, start, len)	SUBSTRING('hello', 2, 3)	'ell'
REPLACE(s, from, to)	REPLACE('foo', 'o', 'a')	'faa'
CONCAT(a, b) or ||	'hello' || ' world'	'hello world'
LEFT(s, n) / RIGHT(s, n)	LEFT('hello', 3)	'hel'

Date/Time Functions

Function	Description
NOW()	Current timestamp with timezone
CURRENT_DATE	Today’s date
DATE_TRUNC('month', ts)	Truncate to start of month
EXTRACT(YEAR FROM ts)	Extract a field from timestamp
AGE(ts1, ts2)	Interval between two timestamps

See SQL Data Types for date types and interval syntax.

Conditional Functions

-- COALESCE: first non-null value
SELECT COALESCE(nickname, first_name, 'Unknown') FROM users;
 
-- NULLIF: returns NULL if both values are equal
SELECT NULLIF(discount, 0);  -- avoids division by zero
 
-- CASE: inline conditional logic
SELECT name,
    CASE
        WHEN score >= 90 THEN 'A'
        WHEN score >= 80 THEN 'B'
        ELSE 'C'
    END AS grade
FROM students;

User-Defined Functions

CREATE OR REPLACE FUNCTION calculate_tax(price DECIMAL, rate DECIMAL DEFAULT 0.08)
RETURNS DECIMAL AS $$
BEGIN
    RETURN price * rate;
END;
$$ LANGUAGE plpgsql;
 
-- Use in a query
SELECT name, price, calculate_tax(price) AS tax FROM products;

References

• Official Documentation