OUT parameters in PostgreSQL - Time & Space Complexity

Time Complexity: OUT parameters

O(n)

Understanding Time Complexity

We want to understand how the time it takes to run a function with OUT parameters changes as the input grows.

Specifically, how does the function's work increase when it returns results through OUT parameters?

Scenario Under Consideration

Analyze the time complexity of the following PostgreSQL function using OUT parameters.

CREATE OR REPLACE FUNCTION get_user_stats(user_id INT, OUT post_count INT, OUT comment_count INT) AS $$
BEGIN
  SELECT COUNT(*) INTO post_count FROM posts WHERE author_id = user_id;
  SELECT COUNT(*) INTO comment_count FROM comments WHERE author_id = user_id;
END;
$$ LANGUAGE plpgsql;

This function counts how many posts and comments a user has, returning the counts through OUT parameters.

Identify Repeating Operations

Look for repeated work inside the function.

• Primary operation: Counting rows in two separate tables filtered by user_id.
• How many times: Each COUNT scans all matching rows once per table.

How Execution Grows With Input

The time depends on how many posts and comments the user has.

Input Size (n)	Approx. Operations
10 posts/comments	Scans about 10 rows in posts and 10 in comments
100 posts/comments	Scans about 100 rows in posts and 100 in comments
1000 posts/comments	Scans about 1000 rows in posts and 1000 in comments

Pattern observation: The work grows roughly in direct proportion to the number of posts and comments the user has.

Final Time Complexity

Time Complexity: O(n)

This means the time to run the function grows linearly with the number of rows it counts for the user.

Common Mistake

[X] Wrong: "Using OUT parameters makes the function run faster because it returns multiple values at once."

[OK] Correct: OUT parameters only change how results are returned, not how much work the function does to get those results.

Interview Connect

Understanding how functions with OUT parameters behave helps you explain performance clearly and shows you know how database functions work under the hood.

Self-Check

"What if the function counted posts and comments for all users instead of one user? How would the time complexity change?"