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?"

Practice

1. What is the purpose of the CREATE FUNCTION statement in PostgreSQL?

easy

A. To delete rows from a table

B. To create a new table in the database

C. To insert data into an existing table

D. To define a reusable block of code that can be called later

Solution

1. Step 1: Understand the role of functions in PostgreSQL

   Functions store reusable code inside the database to perform tasks repeatedly.

2. Step 2: Identify what CREATE FUNCTION does

   This statement defines a new function with parameters, return type, and body.

3. Final Answer:

   To define a reusable block of code that can be called later -> Option D

4. Quick Check:

   CREATE FUNCTION defines reusable code [OK]

Hint: Functions store reusable code blocks in the database [OK]

Common Mistakes:

• Confusing function creation with table creation
• Thinking it inserts or deletes data directly
• Mixing up functions with SQL commands like SELECT or DELETE

2. Which of the following is the correct basic syntax to create a function in PostgreSQL that returns an integer?

easy

A. CREATE FUNCTION myfunc() RETURNS int AS $$ BEGIN RETURN 1; END; $$ LANGUAGE plpgsql;

B. CREATE FUNCTION myfunc RETURNS int AS $$ RETURN 1; $$ LANGUAGE plpgsql;

C. CREATE FUNCTION myfunc() RETURNS integer BEGIN RETURN 1; END LANGUAGE plpgsql;

D. CREATE FUNCTION myfunc() RETURNS int AS BEGIN RETURN 1; END LANGUAGE plpgsql;

Solution

1. Step 1: Check the correct syntax for function creation

   The syntax requires parentheses after the function name, the RETURNS clause, the function body enclosed in $$, and the LANGUAGE specified.

2. Step 2: Validate each option

   CREATE FUNCTION myfunc() RETURNS int AS $$ BEGIN RETURN 1; END; $$ LANGUAGE plpgsql; correctly uses parentheses, RETURNS int, AS $$ ... $$, and LANGUAGE plpgsql. Others miss parentheses, AS $$, or semicolons.

3. Final Answer:

   CREATE FUNCTION myfunc() RETURNS int AS $$ BEGIN RETURN 1; END; $$ LANGUAGE plpgsql; -> Option A

4. Quick Check:

   Correct syntax includes parentheses, RETURNS, AS $$, LANGUAGE [OK]

Hint: Always use () after function name and AS $$ for body [OK]

Common Mistakes:

• Omitting parentheses after function name
• Missing AS $$ ... $$ around function body
• Not specifying LANGUAGE plpgsql
• Forgetting semicolons inside function body

3. Given the function below, what will be the output of SELECT add_one(5);?

CREATE FUNCTION add_one(x integer) RETURNS integer AS $$ BEGIN RETURN x + 1; END; $$ LANGUAGE plpgsql;

medium

A. 6

B. Syntax error

C. 5

D. NULL

Solution

1. Step 1: Understand the function logic

   The function takes an integer input x and returns x + 1.

2. Step 2: Apply the input value 5

   Calling add_one(5) returns 5 + 1 = 6.

3. Final Answer:

   6 -> Option A

4. Quick Check:

   Input 5 plus 1 equals 6 [OK]

Hint: Function adds 1 to input, so 5 becomes 6 [OK]

Common Mistakes:

• Confusing input and output values
• Expecting syntax error due to unfamiliarity
• Assuming function returns NULL without reason

4. Identify the error in the following function definition:

CREATE FUNCTION multiply_by_two(x integer) RETURNS integer AS $$ BEGIN RETURN x * 2 END; $$ LANGUAGE plpgsql;

medium

A. Missing RETURNS clause

B. Incorrect function name syntax

C. Missing semicolon after RETURN statement

D. LANGUAGE plpgsql is not allowed

Solution

1. Step 1: Review function body syntax

   In PL/pgSQL, each statement inside the function body must end with a semicolon.

2. Step 2: Locate missing semicolon

   The RETURN statement lacks a semicolon after x * 2, causing a syntax error.

3. Final Answer:

   Missing semicolon after RETURN statement -> Option C

4. Quick Check:

   Statements inside function need semicolons [OK]

Hint: Check for semicolons after each statement inside function [OK]

Common Mistakes:

• Forgetting semicolon after RETURN
• Misplacing LANGUAGE clause
• Omitting RETURNS clause
• Using invalid function names

5. You want to create a PostgreSQL function concat_names that takes two text parameters and returns their concatenation separated by a space. Which of the following is the correct function definition?

hard

A. CREATE FUNCTION concat_names(a text, b text) RETURNS text AS $$ RETURN a + ' ' + b; $$ LANGUAGE plpgsql;

B. CREATE FUNCTION concat_names(a text, b text) RETURNS text AS $$ BEGIN RETURN a || ' ' || b; END; $$ LANGUAGE plpgsql;

C. CREATE FUNCTION concat_names(a text, b text) RETURNS text AS $$ BEGIN RETURN concat(a, ' ', b); END $$ LANGUAGE plpgsql;

D. CREATE FUNCTION concat_names(a text, b text) RETURNS text AS $$ BEGIN RETURN a & ' ' & b; END; $$ LANGUAGE plpgsql;

Solution

1. Step 1: Understand string concatenation in PostgreSQL

   PostgreSQL uses the || operator to concatenate strings.

2. Step 2: Evaluate each option's concatenation method

   CREATE FUNCTION concat_names(a text, b text) RETURNS text AS $$ BEGIN RETURN a || ' ' || b; END; $$ LANGUAGE plpgsql; uses || correctly with BEGIN...END and semicolons. CREATE FUNCTION concat_names(a text, b text) RETURNS text AS $$ RETURN a + ' ' + b; $$ LANGUAGE plpgsql; uses + which is invalid for text. CREATE FUNCTION concat_names(a text, b text) RETURNS text AS $$ BEGIN RETURN concat(a, ' ', b); END $$ LANGUAGE plpgsql; misses semicolon after END. CREATE FUNCTION concat_names(a text, b text) RETURNS text AS $$ BEGIN RETURN a & ' ' & b; END; $$ LANGUAGE plpgsql; uses & which is invalid.

3. Final Answer:

   CREATE FUNCTION concat_names(a text, b text) RETURNS text AS $$ BEGIN RETURN a || ' ' || b; END; $$ LANGUAGE plpgsql; -> Option B

4. Quick Check:

   Use || for text concatenation in PL/pgSQL [OK]

Hint: Use || operator for text concatenation in PostgreSQL functions [OK]

Common Mistakes:

• Using + or & instead of || for strings
• Forgetting semicolon after END
• Missing BEGIN...END block for multiple statements