Getting to Know PL/pgSQL

PostgreSQL can "speak" more than just SQL! Meet PL/pgSQL (Procedural Language/PostgreSQL Structured Query Language), a procedural programming language that's built into PostgreSQL. It's designed to make complex operations easier using SQL queries and control structures like conditions (IF, CASE) and loops (LOOP, FOR, WHILE).

PL/pgSQL and SQL are kinda like an automated assembly line versus a regular "manual" tool like a hammer. SQL is reliable, just like a hammer. But for every nail, you gotta swing and hit it yourself. PL/pgSQL, though, is like an automatic assembly line: you set up what needs to be done once, and then it just runs—executes commands, remembers stuff, checks if something goes wrong, and keeps going. Why hammer every nail yourself when you can just watch it all happen automatically? Sounds like a dream, right?

PL/pgSQL vs. Regular SQL Queries

Let's break down how PL/pgSQL is different from plain old SQL queries.

SQL is awesome when you need to do one specific thing: like select all students from a table or add a new row. But as soon as you run into logic like "if this, then that" or need to repeat the same actions, SQL starts to struggle.

For example, imagine you need to:

1. Check if there's already a record about a student in the table.
2. If there is—update the data.
3. If there isn't—add a new record.

With regular SQL, you'd have to write several queries and handle the results on the client side. With PL/pgSQL, you can squeeze all that into one procedure that runs right on the database side.

PL/pgSQL really shines in more complex projects. It lets you:

• store intermediate results in variables;
• handle errors so your database doesn't "crash" when something unexpected happens;
• use loops and conditions to run complex algorithms.

Where PL/pgSQL Is Used

PL/pgSQL gets used a lot in these scenarios:

1. Task automation. For example, automatically creating log entries (log) every time data gets updated.

2. Business logic. Say you have a rule: every student with more than 5 absences should automatically be marked as "inactive." Why not put that rule into a PL/pgSQL function?

3. Analytics and reports. Instead of shuffling data back and forth between your database and client app, you can aggregate it right on the server side.

4. Triggers. PL/pgSQL is used to write triggers that automatically do stuff when data is inserted, updated, or deleted.

PL/pgSQL Example

Here's an example of a function that takes two numbers, adds them, and returns the result:

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

Explanation:

• CREATE FUNCTION add_numbers(a INT, b INT) — we're creating a function called add_numbers that takes two arguments of type INT (integer).
• RETURNS INT — says the function returns an integer.
• BEGIN ... END — this is the main instruction block where the function's logic happens.
• RETURN a + b; — the result of the addition gets sent back.

To call this function, just use a regular SQL query:

SELECT add_numbers(10, 20);

Result: 30.

Gotchas and Limitations

PL/pgSQL is a powerful tool, but you gotta be careful with it:

• Performance: super complex functions can slow your database down. Always test and optimize.
• Testing: bugs in functions can go unnoticed until you actually call them. Don't forget to write tests!
• Readability: big functions and procedures with gnarly logic can be tough to understand. Try to write code you'll still get a month from now.

Now that you know a bit about what PL/pgSQL is, let's talk more about its syntax and main features. We'll learn how to create code blocks, declare variables, and work with data types in the next lectures. Let's get to coding on the database side!

2

Task

SQL SELF, level 49, lesson 0

Locked

Creating a Simple Addition Function

Creating a Simple Addition Function

Open