Why advanced PL/pgSQL matters in PostgreSQL - See It in Action

Why advanced PL/pgSQL matters

📖 Scenario: You work at a company that uses PostgreSQL to manage its data. You want to learn how advanced PL/pgSQL features can help you write better database functions that are faster, easier to maintain, and more powerful.

🎯 Goal: Build a simple PL/pgSQL function step-by-step that demonstrates the value of advanced features like variables, control structures, and error handling.

📋 What You'll Learn

Create a PL/pgSQL function with input parameters

Declare variables inside the function

Use IF statements to control logic

Implement exception handling with BEGIN...EXCEPTION blocks

💡 Why This Matters

🌍 Real World

Advanced PL/pgSQL lets you write powerful database functions that run inside PostgreSQL, improving performance and maintainability.

💼 Career

Database developers and backend engineers use PL/pgSQL to implement business logic close to the data, making applications faster and more reliable.

1

Create a basic PL/pgSQL function

Write a PL/pgSQL function named calculate_discount that takes one parameter price of type numeric and returns a numeric. Start the function with the correct CREATE FUNCTION syntax and declare the function body with BEGIN and END.

PostgreSQL

CREATE FUNCTION calculate_discount(price numeric) RETURNS numeric AS $$
BEGIN
    -- Your code here
END;
$$ LANGUAGE plpgsql;

Hint

Use CREATE FUNCTION with the function name and parameter. Use BEGIN and END to define the function body.

2

Add a variable to hold the discount

Inside the calculate_discount function, declare a variable named discounted_price of type numeric. Initialize it to price. Use the DECLARE section for the variable declaration.

PostgreSQL

CREATE FUNCTION calculate_discount(price numeric) RETURNS numeric AS $$
DECLARE
    -- Declare discounted_price here
BEGIN
    RETURN price;
END;
$$ LANGUAGE plpgsql;

Hint

Use DECLARE to create variables before BEGIN. Initialize discounted_price with price.

3

Use IF statement to apply discount

Add an IF statement inside the function body that checks if price is greater than 100. If true, set discounted_price to 90% of price. Otherwise, keep it as price. Use the IF ... THEN ... ELSE ... END IF; syntax.

PostgreSQL

CREATE FUNCTION calculate_discount(price numeric) RETURNS numeric AS $$
DECLARE
    discounted_price numeric := price;
BEGIN
    -- Add IF statement here
    RETURN discounted_price;
END;
$$ LANGUAGE plpgsql;

Hint

Use IF to check the price and update discounted_price accordingly.

4

Add exception handling for invalid input

Wrap the function body inside a BEGIN ... EXCEPTION ... END block. Add an EXCEPTION WHEN others THEN clause that returns NULL if any error occurs (for example, if price is NULL).

PostgreSQL

CREATE FUNCTION calculate_discount(price numeric) RETURNS numeric AS $$
DECLARE
    discounted_price numeric := price;
BEGIN
    -- Add exception handling here
    IF price > 100 THEN
        discounted_price := price * 0.9;
    ELSE
        discounted_price := price;
    END IF;
    RETURN discounted_price;
END;
$$ LANGUAGE plpgsql;

Hint

Use a nested BEGIN ... EXCEPTION ... END block to catch errors and return NULL.

Practice

1. What is one main benefit of using advanced PL/pgSQL in PostgreSQL?

easy

A. It replaces the need for any SQL queries.

B. It disables database transactions.

C. It automatically creates user interfaces.

D. It allows writing complex logic inside the database for better performance.

Solution

1. Step 1: Understand PL/pgSQL purpose

   PL/pgSQL is designed to write procedural code inside PostgreSQL to handle complex logic.

2. Step 2: Identify the benefit

   Writing logic inside the database improves performance by reducing data transfer and centralizing processing.

3. Final Answer:

   It allows writing complex logic inside the database for better performance. -> Option D

4. Quick Check:

   Advanced PL/pgSQL improves performance [OK]

Hint: Think about why logic inside DB helps speed [OK]

Common Mistakes:

• Thinking PL/pgSQL replaces all SQL queries
• Confusing PL/pgSQL with UI tools
• Assuming it disables transactions

2. Which of the following is the correct way to declare a variable in PL/pgSQL?

easy

A. DECLARE myvar INTEGER := 10;

B. myvar INTEGER := 10;

C. DECLARE myvar := 10 INTEGER;

D. VAR myvar INTEGER = 10;

Solution

1. Step 1: Recall PL/pgSQL variable declaration syntax

   Variables are declared inside a DECLARE block with type and optional initialization.

2. Step 2: Check each option

   DECLARE myvar INTEGER := 10; correctly uses DECLARE, variable name, type, and initialization. Others have syntax errors.

3. Final Answer:

   DECLARE myvar INTEGER := 10; -> Option A

4. Quick Check:

   Variable declaration needs DECLARE and type [OK]

Hint: Remember DECLARE block is mandatory for variables [OK]

Common Mistakes:

• Omitting DECLARE keyword
• Placing type after initialization
• Using VAR instead of DECLARE

3. What will be the output of this PL/pgSQL function?

CREATE OR REPLACE FUNCTION add_numbers(a INTEGER, b INTEGER) RETURNS INTEGER AS $$
BEGIN
  RETURN a + b;
END;
$$ LANGUAGE plpgsql;

SELECT add_numbers(3, 5);

medium

A. 8

B. 35

C. Syntax error

D. NULL

Solution

1. Step 1: Understand function logic

   The function takes two integers and returns their sum using RETURN a + b.

2. Step 2: Evaluate the SELECT call

   Calling add_numbers(3, 5) returns 3 + 5 = 8.

3. Final Answer:

   8 -> Option A

4. Quick Check:

   3 + 5 = 8 [OK]

Hint: Add the two input numbers as the function returns sum [OK]

Common Mistakes:

• Concatenating numbers as strings
• Expecting syntax error due to missing semicolon
• Assuming NULL return without explicit return

4. Identify the error in this PL/pgSQL block:

DO $$
DECLARE
  counter INTEGER := 0
BEGIN
  counter := counter + 1;
  RAISE NOTICE 'Counter: %', counter;
END;
$$ LANGUAGE plpgsql;

medium

A. Variable counter cannot be initialized

B. RAISE NOTICE syntax is incorrect

C. Missing semicolon after variable declaration

D. LANGUAGE plpgsql is not allowed in DO blocks

Solution

1. Step 1: Check variable declaration syntax

   In PL/pgSQL, each statement must end with a semicolon. The declaration line lacks a semicolon.

2. Step 2: Verify other parts

   RAISE NOTICE syntax is correct, variable initialization is allowed, and LANGUAGE plpgsql is required.

3. Final Answer:

   Missing semicolon after variable declaration -> Option C

4. Quick Check:

   Statements must end with semicolon [OK]

Hint: Check semicolons after DECLARE lines [OK]

Common Mistakes:

• Ignoring missing semicolon errors
• Misreading RAISE NOTICE syntax
• Thinking variable initialization is disallowed

5. You want to create a PL/pgSQL function that returns the factorial of a number using recursion. Which of these function definitions correctly implements this?

hard

A. CREATE FUNCTION factorial(n INTEGER) RETURNS INTEGER AS $$ BEGIN RETURN n * factorial(n - 1); END; $$ LANGUAGE plpgsql;

B. CREATE 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;

C. CREATE FUNCTION factorial(n INTEGER) RETURNS INTEGER AS $$ BEGIN IF n = 0 THEN RETURN 0; ELSE RETURN n * factorial(n - 1); END IF; END; $$ LANGUAGE plpgsql;

D. CREATE FUNCTION factorial(n INTEGER) RETURNS INTEGER AS $$ BEGIN WHILE n > 1 LOOP RETURN n * factorial(n - 1); END LOOP; END; $$ LANGUAGE plpgsql;

Solution

1. Step 1: Understand factorial base case

   Factorial of 0 or 1 is 1, so base case must return 1 when n <= 1.

2. Step 2: Check recursive call correctness

   CREATE 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; correctly returns 1 for base case and multiplies n by factorial(n-1) otherwise.

3. Step 3: Identify errors in other options

   CREATE FUNCTION factorial(n INTEGER) RETURNS INTEGER AS $$ BEGIN RETURN n * factorial(n - 1); END; $$ LANGUAGE plpgsql; lacks base case, causing infinite recursion. CREATE FUNCTION factorial(n INTEGER) RETURNS INTEGER AS $$ BEGIN IF n = 0 THEN RETURN 0; ELSE RETURN n * factorial(n - 1); END IF; END; $$ LANGUAGE plpgsql; returns 0 for n=0, which is incorrect. CREATE FUNCTION factorial(n INTEGER) RETURNS INTEGER AS $$ BEGIN WHILE n > 1 LOOP RETURN n * factorial(n - 1); END LOOP; END; $$ LANGUAGE plpgsql; misuses WHILE loop and RETURN inside loop.

4. Final Answer:

   Correctly implements recursive factorial with base case and recursion. -> Option B

5. Quick Check:

   Base case + recursion needed for factorial [OK]

Hint: Always include base case in recursion [OK]

Common Mistakes:

• Missing base case causing infinite recursion
• Returning wrong value for factorial(0)
• Using loops incorrectly with RETURN inside