Why advanced PL/pgSQL matters in PostgreSQL - Challenge Your Understanding

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this PL/pgSQL function call?

Consider the following PL/pgSQL function that calculates factorial recursively. What will be the output of SELECT factorial(4);?

PostgreSQL

CREATE OR REPLACE FUNCTION factorial(n integer) RETURNS integer AS $$
BEGIN
  IF n <= 1 THEN
    RETURN 1;
  ELSE
    RETURN n * factorial(n - 1);
  END IF;
END;
$$ LANGUAGE plpgsql;

A 24

B 10

C Error: function calls itself infinitely

D 1

💡 Hint

Think about how factorial is defined mathematically and how recursion works.

The function calculates factorial by multiplying n by factorial of n-1 until n is 1 or less, where it returns 1. So factorial(4) = 4*3*2*1 = 24.

🧠 Conceptual

intermediate

Why use PL/pgSQL over plain SQL for complex logic?

Which of the following is the best reason to use PL/pgSQL instead of plain SQL for database operations?

A PL/pgSQL automatically creates indexes for tables.

B PL/pgSQL runs faster than SQL for simple SELECT queries.

C PL/pgSQL allows procedural logic like loops and conditionals inside the database.

D PL/pgSQL replaces the need for any SQL queries.

💡 Hint

Think about what plain SQL can and cannot do inside the database.

PL/pgSQL supports procedural programming features such as loops, conditionals, and variables, which plain SQL does not support, making it suitable for complex logic.

📝 Syntax

advanced

Identify the syntax error in this PL/pgSQL block

What is the syntax error in the following PL/pgSQL code snippet?

PostgreSQL

DO $$
DECLARE
  counter integer := 0;
BEGIN
  WHILE counter < 5 LOOP
    RAISE NOTICE 'Counter is %', counter;
    counter := counter + 1;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

A Missing semicolon after variable declaration

B Incorrect loop syntax

C RAISE NOTICE requires double quotes

D Missing BEGIN keyword

💡 Hint

Check punctuation after DECLARE section variables.

In PL/pgSQL, each variable declaration must end with a semicolon. The line 'counter integer := 0' is missing a semicolon.

❓ optimization

advanced

Which PL/pgSQL approach improves performance for bulk inserts?

You want to insert 10,000 rows efficiently using PL/pgSQL. Which method is best for performance?

A Use a FOR loop with individual INSERT statements inside

B Use RAISE NOTICE to log each insert

C Use EXECUTE inside the loop for each insert

D Use a single INSERT with multiple VALUES rows

💡 Hint

Minimize the number of separate insert commands.

A single INSERT statement with multiple VALUES rows reduces overhead and improves performance compared to many individual inserts.

🔧 Debug

expert

Why does this PL/pgSQL function raise an exception?

Given this function, why does calling SELECT divide(10, 0); raise an error?

PostgreSQL

CREATE OR REPLACE FUNCTION divide(a integer, b integer) RETURNS numeric AS $$
BEGIN
  RETURN a / b;
END;
$$ LANGUAGE plpgsql;

A Function syntax is invalid due to missing RETURN type

B Division by zero error occurs because no check prevents dividing by zero

C PL/pgSQL does not support division operator

D Function must use EXECUTE for arithmetic operations

💡 Hint

Think about what happens when dividing by zero in SQL.

Dividing by zero causes a runtime error. The function does not check if b is zero before dividing, so it raises an exception.