PostgreSQL — Functions

Basic Function

CREATE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER AS $$
BEGIN
    RETURN a + b;
END;
$$ LANGUAGE plpgsql;

-- Call
SELECT add_numbers(5, 3);

Function with Query

CREATE FUNCTION get_user_count()
RETURNS INTEGER AS $$
DECLARE
    count INTEGER;
BEGIN
    SELECT COUNT(*) INTO count FROM users;
    RETURN count;
END;
$$ LANGUAGE plpgsql;

Function with OUT Parameters

CREATE FUNCTION get_user_stats(
    OUT user_count INTEGER,
    OUT avg_age NUMERIC
) AS $$
BEGIN
    SELECT COUNT(*), AVG(age) INTO user_count, avg_age FROM users;
END;
$$ LANGUAGE plpgsql;

Drop Function

DROP FUNCTION add_numbers(INTEGER, INTEGER);

Function Types

Language	Description
plpgsql	PostgreSQL procedural
sql	Pure SQL
plpython	Python
plv8	JavaScript

Mini Practice

1. Create a function
2. Use parameters
3. Return values
4. Call functions

Up Next

Continue with Procedures — stored procedures.