User-Defined Functions in PostgreSQL: Explained

Image Created by Ceren Çağlayan, Using ChatGPT

In addition to the built-in system functions in SQL (such as max(), min(), avg()), PostgreSQL allows users to create User-Defined Functions (UDFs). These functions enable developers to encapsulate logic in the database, making queries easier to write, maintain, and reuse. UDFs also can improve readability, maintainability, and sometimes even performance.

Get Ceren Çağlayan’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

User-defined functions in PostgreSQL can be divided into three main categories:

1. Scalar Functions
2. Inline Table-Valued Functions (ITVF)
3. Multi-Statement Table-Valued Functions (MSTVF)

I will show you each type with examples. Before demonstrating the examples, let’s assume we have a table named Student with fields: id, name, surname,phone,school_id and score. Also, since PostgreSQL (17) is used, let’s not forget that the syntax will have a slightly different than classical SQL.

Student Table

1- Scalar Function

• Scalar functions return a single value for each input. Typical examples include mathematical calculations or string concatenations for database.
• In our example, we will create a function that returns the letter grade of a student based on their numeric score by looking at our value in the “score” column:

CREATE OR REPLACE FUNCTION get_letter_grade(score float8)
RETURNS text AS $$
BEGIN
    IF score IS NULL THEN
        RETURN NULL;
    ELSIF score >= 90 THEN
        RETURN 'AA';
    ELSIF score >= 85 THEN
        RETURN 'BA';
    ELSIF score >= 80 THEN
        RETURN 'BB';
    ELSIF score >= 75 THEN
        RETURN 'CB';
    ELSIF score >= 70 THEN
        RETURN 'CC';
    ELSIF score >= 65 THEN
        RETURN 'CD';
    ELSIF score >= 60 THEN
        RETURN 'DD';
    ELSE
        RETURN 'FF';
    END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

• This function takes a numeric column score and returns the corresponding letter grade according to the rules defined.

Now, let's see the letter grades! For better understanding, let’s also see students’ corresponding names with our query:

SELECT 
    s.name || ' ' || s.surname AS full_name,
    s.score,
    public.get_letter_grade(s.score) AS letter_grade
FROM "Student" s;

Letter Grades for Each Student in the Student Table by Using Scalar UDF

As you can see, we were able to quickly display each student’s letter grade with the scalar UDF we defined.

2- Inline Table-Valued Functions (ITVF)

• Inline Table-Valued Functions return an entire table as the result. They are essentially a way to abbreviate a SELECT query as a reusable user-defined function.
• For example, let’s create a function that returns all students who failed a course (those with an 'FF' grade):

CREATE OR REPLACE FUNCTION get_failed_students()
RETURNS TABLE(
    id uuid,
    full_name text,
    score  float8,
    letter_grade text
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        s.id,
        s.name || ' ' || s.surname AS full_name,
        s.score,
        public.get_letter_grade(s.score) AS letter_grade
    FROM "Student" s
    WHERE public.get_letter_grade(s.score) = 'FF';
END;
$$ LANGUAGE plpgsql STABLE;

• In this function, we create the table using the columns from the “Student” table. Additionally, we filter students by letter grade as desired using the get_letter_grade(score) function we created earlier.

So, let’s see failed students by using get_failed_students():

SELECT * FROM get_failed_students();

Failed Students in Student Table by Using ITVF

3- Multi-Statement Table-Valued Functions (MSTVF)

• Multi-Statement Table-Valued Functions allow for more complex logic than inline TVFs, so that is not limited to the SELECT function. They can include multiple statements, loops, conditional logic, and still return a table.
• For instance, suppose we want to create a function that adds an is_passed Boolean column to indicate whether a student passed the course or not:

The difference from inline table-valued is that it’s not limited to the select function; it can perform more complex operations. (returns table + query)

CREATE OR REPLACE FUNCTION students_status()
RETURNS TABLE(
    id uuid,
    full_name text,
    score float8,
    letter_grade text,
    is_passed boolean
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        s.id,
        s.name || ' ' || s.surname AS full_name,
        s.score,
        public.get_letter_grade(s.score) AS letter_grade,
        public.get_letter_grade(s.score) <> 'FF' AS is_passed
    FROM "Student" s;
END;
$$ LANGUAGE plpgsql STABLE;

So, let’s see the result by using students_status():

SELECT * FROM students_status();

“is_passed” Column implementation For All Rows by Using MSTVF

Resources

1- Different Types of SQL Server Functions