Calling Functions from SQL Queries

Imagine you have a function for some heavy calculations or data processing. Without the ability to integrate a function into SQL queries, you’d have to do something like this:

1. Call the function from some programming language (like Python or JavaScript).
2. Pass the result into your SQL query.

That’s an extra step! In PostgreSQL, you can plug functions right into your SQL queries, cutting down code, speeding up operations, and reducing server calls. This is especially handy for:

• Automating calculations.
• Validating data before inserting.
• Modifying existing data.

Calling Functions in SELECT

Let’s start with the basics and see how to use functions in a regular SELECT query. Say you have a students table with info about students. We want to write a function that returns the current age of a student based on their birth date.

Step 1: Writing the function

Let’s create a function called calculate_age that takes a birth date and returns the age:

CREATE OR REPLACE FUNCTION calculate_age(birth_date DATE) RETURNS INT AS $$
BEGIN
    RETURN DATE_PART('year', AGE(NOW(), birth_date))::INT;
END;
$$ LANGUAGE plpgsql;

Step 2: Using the function in a SELECT query

Now we can call this function for every row in the table:

SELECT id, name, calculate_age(birth_date) AS age FROM students;

What’s happening?

• For each row in the students table, the calculate_age function calculates the age.
• The returned value shows up in the age column.

Example result:

See? Nothing complicated, and the result looks clean and pro.

Calling Functions in INSERT

Functions are also super useful when inserting data. For example, let’s say we have a logs table where we record user actions. We want to insert a log message using a function that auto-generates the text.

Step 1: Creating the function

Let’s write a function generate_log_message that takes a username and an action, and returns a message text:

CREATE OR REPLACE FUNCTION generate_log_message(username TEXT, action TEXT) RETURNS TEXT AS $$
BEGIN
    RETURN username || ' performed action: ' || action || ' at ' || NOW();
END;
$$ LANGUAGE plpgsql;

Step 2: Using the function in INSERT

Now let’s insert a message into the logs table, calling the function as we add the row:

INSERT INTO logs (message)
VALUES (generate_log_message('Otto', 'login to site'));

Result:

The function does all the work: formats the text and adds a timestamp. This is a great example of how functions can automate boring stuff.

Calling Functions in UPDATE

You can also use functions to modify data in a table. Let’s say we have a students table, and we want to update the group name using a function to move them to the next course.

Step 1: Creating the function

Let’s write a function promote_student that takes the old group (like 101) and returns the new one (like 201):

CREATE OR REPLACE FUNCTION promote_student(old_group TEXT) RETURNS TEXT AS $$
BEGIN
    RETURN '2' || RIGHT(old_group, LENGTH(old_group) - 1);
END;
$$ LANGUAGE plpgsql;

Step 2: Using the function in UPDATE

Let’s update all students’ groups:

UPDATE students
SET group_name = promote_student(group_name);

Result:

Check out how the function does the update magic: old groups get turned into new strings.

Calling Functions in WHERE Clauses

You can use functions in filter conditions too. Let’s expand our student age example.

Step 1: Filtering by age

Let’s use our calculate_age function to select students older than 20:

SELECT id, name, birth_date
FROM students
WHERE calculate_age(birth_date) > 20;

Result:

Here, the function does the heavy lifting, calculating the age for each student on the fly.

Combining with Aggregate Functions

Let’s make things a bit more interesting. We need to count the total number of students younger than 22. Functions work great with aggregate functions like COUNT().

SELECT COUNT(*)
FROM students
WHERE calculate_age(birth_date) < 22;

What’s happening?

• The calculate_age function is used in the filter.
• COUNT(*) counts the number of rows that match the condition.

Real-life Use Cases

Automating data validation. Say you want to check that all students in the table are in a typical age range (like 18 to 30). Write a function to check this and use it in a WHERE clause.

SELECT id, name
FROM students
WHERE NOT (calculate_age(birth_date) BETWEEN 18 AND 30);

Optimizing data insertion. Imagine you’re working in an online store. Instead of calculating the total order price on the client side, write a function that calculates it right when adding data to the orders table.

INSERT INTO orders (user_id, total_price)
VALUES (1, calculate_total_price(ARRAY[5, 10, 15]));

Common Mistakes When Calling Functions

Once you start using functions in your queries, you might run into some errors. Here are a few common situations and how to fix them:

Missing required permissions. If you’re not the owner of the function or table, PostgreSQL might block the call. Make sure you have permission to execute the function.

Type mismatch. When passing arguments to a function, watch your data types. For example, if the function expects a DATE but you pass a string, you’ll get an error. Use explicit casting:

SELECT calculate_age('2000-01-01'::DATE);

Syntax errors inside functions. If the function throws an error, it can break the whole query. Test your functions carefully before using them.