Interaction Between Functions and Procedures

A couple of levels back, we already touched on procedures and functions in PostgreSQL. Now it's time to dive deeper.

Functions and procedures can work independently, but most of the time, their interaction is what makes the whole system tick. The main convenience is that you can call functions from inside other functions, pass data around, and even get results back.

Functions vs Procedures: What's the Difference?

Let's remember how functions are different from procedures in PostgreSQL:

• Functions (FUNCTION):

  • Return values.
  • You can use them in SELECT.
  • Often used for calculations or transforming data.

• Procedures (PROCEDURE):

  • Don't return values directly.
  • Used for doing stuff like inserting, updating, or deleting data.
  • Called with the CALL command.

Passing Data Between Functions

Getting practical, let's start with a basic example of passing data between a function and a procedure. Basically, data gets passed between functions using parameters and return values.

Here's what calling a function from inside another function looks like:

CREATE OR REPLACE FUNCTION get_student_name(student_id INT)
RETURNS TEXT AS $$
DECLARE
    student_name TEXT;
BEGIN
    -- Get the student's name by their ID
    SELECT name INTO student_name FROM students WHERE id = student_id;

    -- Return the name
    RETURN student_name;
END;
$$ LANGUAGE plpgsql;

You can call this function from another function:

CREATE OR REPLACE FUNCTION welcome_student(student_id INT)
RETURNS TEXT AS $$
DECLARE
    message TEXT;
BEGIN
    -- Get the student's name using another function
    message := 'Welcome, ' || get_student_name(student_id) || '!';

    -- Return the greeting
    RETURN message;
END;
$$ LANGUAGE plpgsql;

1. The get_student_name function returns the student's name by their ID (student_id).
2. In another function — welcome_student — that name is used to create a welcome message.

Note: Getting data with SELECT INTO saves the query result into a PL/pgSQL variable.

Example: Calling Procedures from Functions

Now let's see how to call a procedure from a function. Let's say you have a procedure that logs the time a student logs into the system:

CREATE OR REPLACE PROCEDURE log_student_entry(student_id INT)
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO log_entries(student_id, entry_time)
    VALUES (student_id, NOW());
END;
$$;

Now let's call this procedure from a function, where it logs the entry and returns a message:

CREATE OR REPLACE FUNCTION student_login(student_id INT)
RETURNS TEXT AS $$
BEGIN
    -- Call the procedure to log the entry
    CALL log_student_entry(student_id);

    -- Return a message
    RETURN 'Student login logged successfully.';
END;
$$ LANGUAGE plpgsql;

Practical Examples of Interaction

Example 1: Calculating Order Total and Logging the Order

Imagine you're working with an online order system. To calculate the total order amount, you have a function:

CREATE OR REPLACE FUNCTION calculate_order_total(order_id INT)
RETURNS NUMERIC AS $$
DECLARE
    total NUMERIC;
BEGIN
    -- Sum up all the order items
    SELECT SUM(price * quantity) INTO total
    FROM order_items
    WHERE order_id = order_id;

    RETURN total;
END;
$$ LANGUAGE plpgsql;

To save the total order amount, you use a procedure:

CREATE OR REPLACE PROCEDURE log_order_total(order_id INT, total NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO order_totals(order_id, total)
    VALUES (order_id, total);
END;
$$;

Now let's tie them together:

CREATE OR REPLACE FUNCTION process_order(order_id INT)
RETURNS TEXT AS $$
DECLARE
    total NUMERIC;
BEGIN
    -- Call the function to calculate the total
    total := calculate_order_total(order_id);

    -- Log the total using the procedure
    CALL log_order_total(order_id, total);

    RETURN 'Order processed successfully.';
END;
$$ LANGUAGE plpgsql;

Example 2: Getting a Student's Highest Rating and Updating Their Profile

Function to get the highest rating:

CREATE OR REPLACE FUNCTION get_highest_rating(student_id INT)
RETURNS INT AS $$
DECLARE
    max_rating INT;
BEGIN
    -- Find the student's highest rating
    SELECT MAX(rating) INTO max_rating
    FROM ratings
    WHERE student_id = student_id;

    RETURN max_rating;
END;
$$ LANGUAGE plpgsql;

Procedure to update the student's profile:

CREATE OR REPLACE PROCEDURE update_student_profile(student_id INT, max_rating INT)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE students
    SET highest_rating = max_rating
    WHERE id = student_id;
END;
$$;

Function to call these operations:

CREATE OR REPLACE FUNCTION refresh_student_profile(student_id INT)
RETURNS TEXT AS $$
DECLARE
    max_rating INT;
BEGIN
    -- Get the highest rating
    max_rating := get_highest_rating(student_id);

    -- Update the student's profile
    CALL update_student_profile(student_id, max_rating);

    RETURN 'Profile updated successfully.';
END;
$$ LANGUAGE plpgsql;

Common Mistakes When Interacting

One of the most common mistakes is mismatched data types between a function and a procedure. For example, if your procedure expects a NUMERIC parameter but you pass an INTEGER, PostgreSQL will complain about a type mismatch. Always double-check that your data types match up.

Another mistake is cyclic function calls, where function A calls function B, and B calls A again. This leads to infinite recursion and crashes your system.

Why This Matters

Why do we need this kind of interaction? In real life, functions and procedures work like "building blocks" for complex systems. They let you break your code into independent pieces, which makes debugging, reusing, and testing way easier. For example:

• At a job interview, you might be asked to write a function that calls a procedure to do something complex. Showing off your practical skills with this kind of interaction is a big plus.
• When building real apps like online stores, logging systems, or CRMs, knowing how to organize functionality through function and procedure interaction makes your code way simpler.

To dig deeper into how functions and procedures interact, check out the official PL/pgSQL docs.