Why utility functions matter in PostgreSQL - Challenge Your Understanding

Challenge - 5 Problems

🧠 Conceptual

intermediate

Why use utility functions in SQL?

Which of the following best explains why utility functions are important in SQL databases?

A They help reuse code and simplify complex queries by encapsulating logic.

B They automatically optimize the database storage space.

C They replace the need for indexes on tables.

D They allow direct editing of database files outside SQL.

💡 Hint

Think about how repeating the same code in many places can be avoided.

Utility functions let you write a piece of logic once and use it many times, making queries easier to read and maintain.

❓ query_result

intermediate

Output of a utility function call

Given this utility function in PostgreSQL:

CREATE OR REPLACE FUNCTION add_tax(price numeric) RETURNS numeric AS $$
BEGIN
  RETURN price * 1.1;
END;
$$ LANGUAGE plpgsql;

What is the result of SELECT add_tax(100);?

A Error: function does not exist

B 100.0

C 10.0

D 110.0

💡 Hint

The function adds 10% tax to the price.

The function multiplies the input by 1.1, so 100 becomes 110.

📝 Syntax

advanced

Identify the syntax error in this utility function

Which option contains the correct syntax for a PostgreSQL utility function that returns the square of an integer?

PostgreSQL

CREATE OR REPLACE FUNCTION square_num(n integer) RETURNS integer AS $$
BEGIN
  RETURN n * n;
END;
$$ LANGUAGE plpgsql;

A CREATE OR REPLACE FUNCTION square_num(n integer) RETURNS integer AS $$ BEGIN RETURN n * n; END $$ LANGUAGE plpgsql;

B CREATE OR REPLACE FUNCTION square_num(n integer) RETURNS integer AS $$ BEGIN RETURN n * n; END; $$ LANGUAGE plpgsql;

C CREATE OR REPLACE FUNCTION square_num(n integer) RETURNS integer AS $$ BEGIN RETURN n * n; END; $$ LANGUAGE plpgsql

D CREATE FUNCTION square_num(n integer) RETURNS integer AS $$ BEGIN RETURN n * n END $$ LANGUAGE plpgsql;

💡 Hint

Check for missing semicolons and correct use of language declaration.

Option B correctly includes semicolons and language declaration. Others miss semicolons or language keyword.

❓ optimization

advanced

How utility functions improve query performance

Which statement best describes how utility functions can improve performance in PostgreSQL?

A They automatically create indexes on columns used inside the function.

B They store query results permanently to speed up future queries.

C They allow complex logic to be precompiled and reused, reducing parsing and planning time.

D They replace the need for database normalization.

💡 Hint

Think about what happens when a function is called multiple times in queries.

Utility functions are compiled once and reused, which can reduce overhead in query execution.

🔧 Debug

expert

Debugging a utility function with unexpected output

Consider this PostgreSQL function:

CREATE OR REPLACE FUNCTION get_discounted_price(price numeric, discount_percent numeric) RETURNS numeric AS $$
BEGIN
  RETURN price - price * discount_percent / 100;
END;
$$ LANGUAGE plpgsql;

When calling SELECT get_discounted_price(200, 20);, the result is 160. But the expected result is 180. What is the most likely cause?

A The function applies the discount correctly; the expected result is wrong.

B The discount_percent should be divided by 10, not 100.

C The function should add the discount instead of subtracting it.

D The discount_percent argument is being treated as a whole number instead of a decimal.

💡 Hint

Calculate 20% of 200 and subtract it from 200.

20% of 200 is 40, so 200 - 40 = 160. The function works correctly; the expected result is incorrect.

Practice

1. Why are utility functions important in PostgreSQL databases?

easy

A. They help reuse code and simplify queries

B. They slow down query execution significantly

C. They make the database schema more complex

D. They prevent any data from being updated

Solution

1. Step 1: Understand the purpose of utility functions

   Utility functions are designed to perform common tasks repeatedly without rewriting code.

2. Step 2: Recognize benefits in query simplification

   By using utility functions, queries become simpler and easier to maintain because logic is centralized.

3. Final Answer:

   They help reuse code and simplify queries -> Option A

4. Quick Check:

   Utility functions = reuse and simplify [OK]

Hint: Utility functions = reuse code + simplify queries [OK]

Common Mistakes:

• Thinking utility functions slow down queries
• Believing they complicate the database schema
• Assuming they block data updates

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

easy

A. CREATE FUNCTION square(x INT) RETURNS INT AS 'RETURN x * x;' LANGUAGE sql;

B. CREATE FUNCTION square(x INT) RETURNS INT BEGIN RETURN x * x; END;

C. CREATE FUNCTION square(x INT) RETURNS INT AS $$ BEGIN RETURN x * x; END; $$ LANGUAGE plpgsql;

D. CREATE FUNCTION square(x INT) RETURNS INT AS $$ RETURN x * x; $$ LANGUAGE plpgsql;

Solution

1. Step 1: Identify correct function creation syntax

   PostgreSQL requires the function body inside $$ with BEGIN and END for plpgsql language.

2. Step 2: Check for proper RETURNS and LANGUAGE clauses

   CREATE FUNCTION square(x INT) RETURNS INT AS $$ BEGIN RETURN x * x; END; $$ LANGUAGE plpgsql; correctly uses RETURNS INT, BEGIN...END block, and LANGUAGE plpgsql.

3. Final Answer:

   CREATE FUNCTION square(x INT) RETURNS INT AS $$ BEGIN RETURN x * x; END; $$ LANGUAGE plpgsql; -> Option C

4. Quick Check:

   Correct plpgsql function syntax = CREATE FUNCTION square(x INT) RETURNS INT AS $$ BEGIN RETURN x * x; END; $$ LANGUAGE plpgsql; [OK]

Hint: Use $$ with BEGIN...END and specify LANGUAGE plpgsql [OK]

Common Mistakes:

• Omitting BEGIN and END in plpgsql functions
• Missing LANGUAGE clause
• Incorrect function body syntax

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

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

medium

A. 5

B. NULL

C. Error: function not found

D. 10

Solution

1. Step 1: Understand the function logic

   The function takes an integer n and returns n multiplied by 2.

2. Step 2: Apply the input value 5

   Calling multiply_by_two(5) returns 5 * 2 = 10.

3. Final Answer:

   10 -> Option D

4. Quick Check:

   5 * 2 = 10 [OK]

Hint: Multiply input by 2 as function defines [OK]

Common Mistakes:

• Confusing input and output values
• Assuming function returns input unchanged
• Expecting NULL or error without reason

4. You wrote this utility function but get an error when calling it:

CREATE FUNCTION add_five(n INT) RETURNS INT AS $$ BEGIN RETURN n + 5 END; $$ LANGUAGE plpgsql;

What is the likely cause of the error?

medium

A. Wrong data type for parameter

B. Missing semicolon after RETURN statement

C. Incorrect function name

D. LANGUAGE clause is missing

Solution

1. Step 1: Check function syntax carefully

   In plpgsql, each statement must end with a semicolon; the RETURN line lacks one.

2. Step 2: Identify missing semicolon causing syntax error

   Adding a semicolon after RETURN n + 5 fixes the error.

3. Final Answer:

   Missing semicolon after RETURN statement -> Option B

4. Quick Check:

   Semicolon needed after RETURN [OK]

Hint: Always end statements with semicolon in plpgsql [OK]

Common Mistakes:

• Forgetting semicolon after RETURN
• Assuming function name causes error
• Ignoring LANGUAGE clause presence

5. You want to create a utility function that returns the length of a text string but returns 0 if the input is NULL. Which function definition correctly implements this behavior?

hard

A. CREATE FUNCTION safe_length(s TEXT) RETURNS INT AS $$ BEGIN IF s IS NULL THEN RETURN 0; ELSE RETURN LENGTH(s); END IF; END; $$ LANGUAGE plpgsql;

B. CREATE FUNCTION safe_length(s TEXT) RETURNS INT AS $$ BEGIN RETURN COALESCE(s, 0); END; $$ LANGUAGE plpgsql;

C. CREATE FUNCTION safe_length(s TEXT) RETURNS INT AS $$ RETURN LENGTH(s) OR 0; $$ LANGUAGE sql;

D. CREATE FUNCTION safe_length(s TEXT) RETURNS INT AS $$ BEGIN RETURN LENGTH(s); END; $$ LANGUAGE plpgsql;

Solution

1. Step 1: Understand requirement for NULL input handling

   The function must return 0 if input is NULL, else return string length.

2. Step 2: Analyze each option for NULL safety

   CREATE FUNCTION safe_length(s TEXT) RETURNS INT AS $$ BEGIN IF s IS NULL THEN RETURN 0; ELSE RETURN LENGTH(s); END IF; END; $$ LANGUAGE plpgsql; explicitly checks for NULL and returns 0, fulfilling the requirement correctly.

3. Final Answer:

   CREATE FUNCTION safe_length(s TEXT) RETURNS INT AS $$ BEGIN IF s IS NULL THEN RETURN 0; ELSE RETURN LENGTH(s); END IF; END; $$ LANGUAGE plpgsql; -> Option A

4. Quick Check:

   Explicit NULL check returns 0 [OK]

Hint: Use IF NULL THEN return 0 ELSE length END IF [OK]

Common Mistakes:

• Not handling NULL input explicitly
• Using COALESCE incorrectly with LENGTH
• Assuming LENGTH returns 0 for NULL