Creating Simple Functions: CREATE FUNCTION

In PostgreSQL, functions are a powerful tool that let you automate stuff, build business logic, and make your server a bit smarter. Think of functions as mini-programs that run inside your database. They're super handy for:

• Reusing code. If you keep running the same queries over and over, just wrap them in a function and call it whenever you need.
• Automating tasks. For example, if you need to calculate employee salaries based on their work hours, a function can totally handle that.
• Encapsulating logic. This lets you keep all the heavy calculations on the server side, so your clients don't have to mess with complicated SQL queries.

General Syntax for CREATE FUNCTION

Here's what the basic structure for creating a function looks like:

CREATE FUNCTION function_name(parameters) RETURNS return_type AS $$
BEGIN
    -- Function body (logic)
    RETURN result;
END;
$$ LANGUAGE plpgsql;

Let's break down the main parts:

CREATE FUNCTION function_name(parameters):

In this line, we set the function name function_name and specify the parameters (if you need any).

Parameters can have a name and data type: my_param INTEGER, another_param TEXT.

RETURNS return_type:

This tells what our function will return: a single value (INTEGER, TEXT, etc.) or a set of data (TABLE, RECORD).

BEGIN ... END:

Between these keywords is the "body" of the function, where all the magic happens.

RETURN result:

Returns the result of the function. Be careful: the result type has to match what you put in RETURNS.

LANGUAGE plpgsql:

This says we're using the PL/pgSQL language. PostgreSQL supports other languages too, but for now, this is the one we want.

Simple Example: Adding Two Numbers

Let's make a function that returns the sum of two integers.

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

Now let's call it:

SELECT add_numbers(5, 7); -- Result: 12

What's going on here?

• The function takes two parameters a and b of type INT.
• Inside the function, we just add them up (a + b) and return the result.
• It's as simple as a calculator!

Example Using Variables

Let's say we have a university database and we want to know how many students are registered.

Let's create a function:

CREATE FUNCTION count_students() RETURNS INT AS $$
DECLARE
    total INT; -- Declare a variable to store the result
BEGIN
    SELECT COUNT(*) INTO total FROM students; -- Count the number of rows in the table
    RETURN total; -- Return the result
END;
$$ LANGUAGE plpgsql;

Calling the function:

SELECT count_students(); -- Let's say the result is: 120

Here's what we see:

• Using the variable total to store the result of the SQL query.
• The SELECT ... INTO command puts the result of the query into the variable.

This approach is especially handy if you need to process the data first and then return it.

Returning Multiple Values: RETURNS TABLE

In the previous example, we only returned one value. But what if our function needs to return a set of data, like a list of students? That's where RETURNS TABLE comes in handy.

Example:

CREATE FUNCTION get_students() RETURNS TABLE(id INT, name TEXT) AS $$
BEGIN
    RETURN QUERY SELECT id, name FROM students;
END;
$$ LANGUAGE plpgsql;

Calling the function:

SELECT * FROM get_students();

Possible result:

The Power of RETURN QUERY for Running Queries Inside a Function

RETURN QUERY lets us return the result of a SQL query straight from the function. This cuts out extra steps and makes functions simpler.

Let's make a function that only returns students who are active:

CREATE FUNCTION get_active_students() RETURNS TABLE(id INT, name TEXT) AS $$
BEGIN
    RETURN QUERY SELECT id, name FROM students WHERE active = TRUE;
END;
$$ LANGUAGE plpgsql;

Before you call the get_active_students() function, you need to create the students table and fill it with some test data. Here's how you can do it:

-- Create the students table
CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    active BOOLEAN DEFAULT TRUE
);

-- Add a few records
INSERT INTO students (name, active) VALUES
('Alice', FALSE),
('Bob', TRUE),
('Charlie', TRUE),
('Dana', FALSE);

Table:

Now call:

SELECT * FROM get_active_students();

Result:

Checking Data Validity Before Running

Functions can have IF checks to make sure the data is valid. For example, we can make a function to promote a student to the next course only if they've passed all their exams.

Example:

CREATE FUNCTION promote_student(student_id INT) RETURNS TEXT AS $$
DECLARE
    passed_exams INT;
BEGIN
    -- Count the number of exams the student has passed
    SELECT COUNT(*) INTO passed_exams
    FROM exams
    WHERE student_id = promote_student.student_id AND status = 'passed';

    -- Check the condition
    IF passed_exams < 5 THEN
        RETURN 'Student has not passed enough exams';
    END IF;

    -- Update the student's course
    UPDATE students
    SET course = course + 1
    WHERE id = promote_student.student_id;

    RETURN 'Student promoted!';
END;
$$ LANGUAGE plpgsql;

Common Mistakes When Creating Functions

Missing return type. PostgreSQL always needs you to specify what the function will return. For example:

CREATE FUNCTION fail() AS $$ -- Error: no RETURNS
BEGIN
    RETURN 1;
END;
$$ LANGUAGE plpgsql;

Fix:

CREATE FUNCTION succeed() RETURNS INT AS $$
BEGIN
    RETURN 1;
END;
$$ LANGUAGE plpgsql;

Return type mismatch. If you say RETURNS INT, you have to return a number. Trying to return a string here is a bad idea.

Error in SQL queries inside the function. Always check your queries before using them in functions. It's better to test them "by hand" in psql or pgAdmin.