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.