Ever felt like your database is stuck in slow motion? Meet PL/pgSQL, your new best friend in the world of databases. It's the secret sauce that makes PostgreSQL more awesome than ever by introducing something cool: special tools that live right inside your database.

With PL/pgSQL, no need to haul all your data to your computer. Send the job directly to your data, making operations quicker and smoother.

Let's go! 🚀

Starting with basics …

What is Procedural Language (PL)?

Procedural language is a type of programming language that follows a sequential, step-by-step approach to solving problems.

In PostgreSQL, a variety of procedural languages is supported to facilitate user-defined functions, and PL/pgSQL is one of them.

The database server itself doesn't automatically understand how to interpret the source code of these functions. Instead, it delegates this task to a specific handler with the necessary knowledge about the particular language used in the function.

What is PL/pgSQL?

PL/pgSQL is like a turbocharged version of SQL! It's the procedural extension to SQL, combining the power of programming languages with the data manipulation and query capabilities of SQL itself.

• It allows loops, conditions, functions, etc directly in your database.

• Extra round trips between client and server are eliminated.

In PL/pgSQL, the fundamental building block is known as a "block." All PL/pgSQL code consists of one or more blocks, arranged either sequentially or nested within another block. There are two main types of blocks:

1. Anonymous Blocks (DO):

   These blocks are typically constructed dynamically and executed only once by the user. Think of them as complex SQL statements.

2. Named Blocks (Functions and Stored Procedures):

   Named blocks have an associated name, are stored in the database, and can be executed repeatedly. They serve as reusable components and can accept parameters, adding a layer of flexibility to their functionality.

Structure of Anonymous Block

DO $$ 
DECLARE
    -- Variable declarations, if any
BEGIN
    -- SQL statements or procedural code
END $$;

Explanation:

• DO $$:

  Marks the beginning of the anonymous block, where $$ denotes the block boundary.

• DECLARE:

  Optional section where variables, constants, or other declarations can be defined.

• BEGIN:

  Marks the start of the code logic segment.

• END $$;:

  Ends the block, closing it with $$ to match the opening delimiter DO $$.

Like other programming languages, PL/pgSQL also has variables and other things…

Structure of Named Blocks

CREATE FUNCTION [function_name]()
RETURNS [return_type] $$
[<<label>>]
DECLARE
    /* Declare section (optional). */
BEGIN
    /* Executable section (required). */
EXCEPTION
    /* Exception handling section (optional). */
END [label]
$$ LANGUAGE plpgsql;

Explanation:

• CREATE FUNCTION function_name :

  • Defines the creation of a function with an optional name ([function_name]).

• RETURNS [return_type]:

  • Specifies the return type of the function.

• $$:

  • Marks the beginning and end of the function body.

• [<<label>>]:

  • Optional label to mark a specific point in the code.

• DECLARE:

  • Optional section where variables, constants, or other declarations can be defined.

• BEGIN:

  • Marks the start of the executable section, where the main logic of the function resides.

• EXCEPTION:

  • Optional section for handling exceptions or errors.

• END [label]:

  • Marks the end of the function, with an optional label for code organization.

• LANGUAGE plpgsql:

  • Specifies that the function is written in the PL/pgSQL language.

This structure encapsulates the essential components of a named block, allowing for the declaration of variables, execution of logic, and handling of exceptions within a PostgreSQL function.

Function Example

- Dummy Booka data

```sql CREATE TABLE public.books ( book_id SERIAL PRIMARY KEY, title VARCHAR NOT NULL, author VARCHAR NOT NULL, publication_year INTEGER, genre VARCHAR, isbn VARCHAR, price DECIMAL(10, 2), quantity_in_stock INTEGER );

CREATE TABLE public.order_history ( order_id SERIAL PRIMARY KEY, book_id INTEGER REFERENCES public.books(book_id), quantity_ordered INTEGER, order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP );

INSERT INTO public.books (title, author, publication_year, genre, isbn, price, quantity_in_stock) VALUES (

Example 1 - Get Books count

Normal function without parameter

CREATE FUNCTION get_book_count()
    RETURNS integer
AS $$
DECLARE
    v_count integer;
BEGIN
    SELECT count(*)
        INTO v_count
    FROM books;
    RETURN v_count;
END
$$ LANGUAGE plpgsql;

Explanation:

1. Function Definition:

   • The function is named get_book_count and doesn't take any parameters.

   • It returns an integer, indicating the count of books.

2. Declaration Section:

   • It declares a variable v_count of type integer to temporarily store the count of books.

3. Main Logic:

   • The BEGIN to END block contains the main logic of the function.

   • It uses a SELECT count(*) INTO v_count statement to count the rows in the 'books' table and store the count in the variable v_count.

4. Return Statement:

   • The RETURN v_count statement returns the count as the result of the function.

In simpler terms, this function calculates and provides the count of books present in the 'books' table.

These will create function which we can call with select to get value.

SELECT * FROM get_book_count()

>> output
-------------
get_book_count
-------------
      26
(1 row)

Example 2 - Calculate Genre Total

Function with parameters

With your books table structure in mind, let's create a simple PL/pgSQL function that retrieves the total value of books in stock for a specified genre. Here's an example:

CREATE OR REPLACE FUNCTION calculate_genre_total_value(
    p_genre VARCHAR
)
RETURNS DECIMAL(10, 2)
AS $$
DECLARE
    v_total_value DECIMAL(10, 2) := 0;
BEGIN
    -- Calculate the total value of books in stock for the specified genre
    SELECT COALESCE(SUM(price * quantity_in_stock), 0)
    INTO v_total_value
    FROM books
    WHERE genre = p_genre;

    -- Return the total value
    RETURN v_total_value;
EXCEPTION
    WHEN others THEN
        -- Handle exceptions if needed
        RAISE EXCEPTION 'Error in calculate_genre_total_value: %', SQLERRM;
END;
$$ LANGUAGE plpgsql;

Explanation:

• The function, calculate_genre_total_value, takes one parameter: p_genre (the genre of books).

• It uses a SELECT statement to calculate the total value by summing the product of price and quantity_in_stock for books within the specified genre.

• The COALESCE function is used to handle cases where there might be no books in the specified genre.

• The result is stored in the v_total_value variable, and the function returns this total value.

• The EXCEPTION block is included to handle any exceptions that might occur during the execution of the function.

SELECT * FROM calculate_genre_total_value('Fiction')

>> output
-----------------------------
calculate_genre_total_value
-----------------------------
                     2049.52
(1 row)

Example 3 - Get book count in year

• Parameters can have a default value

• This essentially makes them optional parameters

CREATE OR REPLACE FUNCTION get_book_count_by_year(
    p_year INTEGER DEFAULT EXTRACT(YEAR FROM CURRENT_DATE)
)
RETURNS integer
AS $$
-- Declaration Section:
DECLARE
    v_count integer;  -- Declare a variable to store the book count.
BEGIN
    -- Main Logic:
    SELECT COUNT(*)
    INTO v_count
    FROM books
    WHERE publication_year = p_year;

    -- Return Statement:
    RETURN v_count;    -- Return the count as the result of the function.
END
$$ LANGUAGE plpgsql;

SELECT * FROM get_book_count_by_year()
-- default return current year books count

That's a basic use case with functions.

Control the Flow

The logical flow of statements can be changed using conditional

---IF-THEN
IF boolean-expression THEN
-- statements
END IF;

-- IF-THEN-ELSE
IF boolean-expression THEN
-- statements
ELSE
-- statements
END IF;

-- IF-THEN-ELSIF-THEN-ELSE
IF boolean-expression THEN
  -- statements
ELSIF boolean-expression THEN
  -- statements
ELSE
  -- statements
END IF;

Example 4 - Process Order and Update Books Inventory

This function simulates the processing of an order, checking if there's sufficient stock for the requested quantity. If available, it updates the inventory and logs the order details.

CREATE OR REPLACE FUNCTION process_order_and_update_books(
    p_book_id INTEGER,
    p_quantity_ordered INTEGER
)
RETURNS VOID
AS $$
DECLARE
    v_current_stock INTEGER;
BEGIN
    -- Retrieve the current stock for the specified book
    SELECT quantity_in_stock
    INTO v_current_stock
    FROM books
    WHERE book_id = p_book_id;

    -- Check if the requested quantity is available in stock
    IF v_current_stock >= p_quantity_ordered THEN
        -- Update inventory after processing the order
        UPDATE books
        SET quantity_in_stock = quantity_in_stock - p_quantity_ordered
        WHERE book_id = p_book_id;

        -- Log the order details or perform additional actions
        INSERT INTO order_history (book_id, quantity_ordered)
        VALUES (p_book_id, p_quantity_ordered);
    ELSE
        -- Handle insufficient stock scenario
        RAISE EXCEPTION 'Insufficient stock for book_id: %', p_book_id;
    END IF;
EXCEPTION
    WHEN others THEN
        -- Handle exceptions if needed
        RAISE EXCEPTION 'Error in process_order_and_update_inventory: %', SQLERRM;
END;
$$ LANGUAGE plpgsql;

Explanation:

• Returns:

  • RETURNS VOID: Indicates that the function does not return any value

• DECLARE Section:

  • DECLARE v_current_stock INTEGER: Declares a local variable v_current_stock to store the current stock quantity.

• BEGIN...END Block:

  • SELECT quantity_in_stock INTO v_current_stock FROM books WHERE book_id = p_book_id: Retrieves the current stock quantity for the specified book.

  • IF v_current_stock >= p_quantity_ordered THEN: Checks if the requested quantity is available in stock.

    • UPDATE books SET quantity_in_stock = quantity_in_stock - p_quantity_ordered WHERE book_id = p_book_id: Updates the inventory by subtracting the ordered quantity.

    • INSERT INTO order_history (book_id, quantity_ordered) VALUES (p_book_id, p_quantity_ordered);: Logs the order details in the order_history table, capturing the book ID and the ordered quantity.

  • ELSE: Handles the scenario when there is insufficient stock.

    • RAISE EXCEPTION 'Insufficient stock for book_id: %', p_book_id: Raises an exception indicating insufficient stock for the specified book.

• EXCEPTION Section:

  • WHEN others THEN: Catches any exceptions that might occur during the execution of the function.

    • RAISE EXCEPTION 'Error in process_order_and_update_inventory: %', SQLERRM: Raises an exception with an error message if any unexpected errors occur.

SELECT * FROM process_order_and_update_books(3, 2)

-- This will decrease stock by 2 and add entry in order history table.

Conclusion

In our exploration of PL/pgSQL basics, we've touched upon its capabilities using simple examples. While we've seen how it efficiently retrieves aggregated data, this is just the tip of the iceberg. The real magic lies in the intricate and advanced features that await us in the next part of our journey. Brace yourself for a deep dive into the complexities and true potential of PL/pgSQL!

Here are some resources I referenced when writing this blog:

• ChatGPT

• PostgreSQL Documentation