Function creation syntax in PostgreSQL - Time & Space Complexity

Time Complexity: Function creation syntax

O(n)

Understanding Time Complexity

When we create a function in PostgreSQL, it's important to understand how the time it takes to run grows as the function handles more data or more calls.

We want to know how the function's execution time changes when the input or usage increases.

Scenario Under Consideration

Analyze the time complexity of the following function creation code.

CREATE OR REPLACE FUNCTION sum_array(arr integer[])
RETURNS integer AS $$
DECLARE
  total integer := 0;
  i integer;
BEGIN
  FOR i IN 1..array_length(arr, 1) LOOP
    total := total + arr[i];
  END LOOP;
  RETURN total;
END;
$$ LANGUAGE plpgsql;

This function sums all numbers in an integer array by looping through each element.

Identify Repeating Operations

Look for repeated actions inside the function.

• Primary operation: Looping through each element of the array to add it to total.
• How many times: Once for each element in the array (array length times).

How Execution Grows With Input

The time to run grows as the array gets bigger because the function adds each number one by one.

Input Size (n)	Approx. Operations
10	10 additions
100	100 additions
1000	1000 additions

Pattern observation: The number of operations grows directly with the size of the input array.

Final Time Complexity

Time Complexity: O(n)

This means the time to complete the function grows in a straight line as the input array gets bigger.

Common Mistake

[X] Wrong: "The function runs in the same time no matter how big the array is."

[OK] Correct: Because the function adds each element one by one, more elements mean more work and more time.

Interview Connect

Understanding how function execution time grows helps you write efficient database code and explain your reasoning clearly in conversations.

Self-Check

"What if we changed the function to sum only the first half of the array? How would the time complexity change?"