Comparing PL/pgSQL with Other Procedural Programming Languages (PL/SQL, T-SQL)

In the database world, there are a few languages that take regular SQL to the next level and let you write full-on business logic right inside your DBMS. Each one is tailored for its own platform, but overall, they solve similar problems—automating, simplifying, and speeding up how you work with data. Some of these languages are PL/pgSQL for PostgreSQL, PL/SQL for Oracle, and T-SQL for SQL Server. Each has its own quirks, strengths, and gotchas, and that's what we're gonna talk about now.

PL/pgSQL (Procedural Language/PostgreSQL Structured Query Language) is a procedural programming language built into PostgreSQL. Its main job is to beef up SQL by giving devs stuff like variables, loops, control structures, and error handling blocks. This makes it a powerful tool for putting complex business logic right in your database.

PL/SQL (Procedural Language/SQL) is a procedural programming language baked into Oracle DBMS. It gives you similar tools for data processing and lets you create procedures, functions, and packages. PL/SQL is considered super mature thanks to decades of refinement and a rich ecosystem of tools.

T-SQL (Transact-SQL) is a language made by Microsoft for SQL Server. It's an extension of standard SQL, adding support for variables, control structures, and other procedural programming stuff. T-SQL has its own flavor when it comes to transactions, cursors, and working with JSON.

Similarities Between PL/pgSQL, PL/SQL, and T-SQL

At first glance, all three languages look pretty similar. No surprise there, since their main goal is the same—to help you put business logic right inside the database. Let's check out the main things they have in common:

1. Block Syntax

   All three languages give you a structured way to write procedural code. The main elements:

   • Declaring variables.
   • Main execution block (BEGIN ... END).
   • Support for exception handling.

2. Variables

   You can declare and use variables in any of these languages. Here's how you declare a variable in PL/pgSQL:

   DECLARE
       student_id INT;
   BEGIN
       student_id := 10;
   END;
   

   You can do pretty much the same thing in PL/SQL and T-SQL.

3. Control Structures

   All the languages support IF...THEN, CASE, LOOP, FOR, WHILE, so you can write some pretty gnarly algorithms.

4. Functions and Procedures

   You can create and call your own functions and procedures, which can return single values or even tables.

Differences Between PL/pgSQL, PL/SQL, and T-SQL

Devs often run into situations where they need to switch from one database to another. In those cases, it's important to know the little differences between the languages. Let's break down the key differences.

Declaring Variables

PL/pgSQL: variables are declared in the DECLARE block. You assign values using :=.

DECLARE
    total_students INT;
BEGIN
    total_students := 5;
END;

PL/SQL: declaring is pretty much like PL/pgSQL, but you can inherit the variable type from a table column using %TYPE.

DECLARE
    student_name students.name%TYPE;
BEGIN
    student_name := 'John';
END;

T-SQL: variables are declared with the DECLARE keyword, and you assign values using SET or SELECT.

DECLARE @total_students INT;
SET @total_students = 5;  -- or
SELECT @total_students = COUNT(*) FROM students;

Error Handling

PL/pgSQL: uses the EXCEPTION block for error handling. For example:

BEGIN
    SELECT * INTO my_var FROM nonexistent_table;
EXCEPTION
    WHEN others THEN
        RAISE NOTICE 'An error occurred!';
END;

PL/SQL: also uses EXCEPTION, but with more detailed error types.

BEGIN
    SELECT * INTO my_var FROM nonexistent_table;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('No data found!');
END;

T-SQL: uses TRY...CATCH blocks.

BEGIN TRY
    SELECT 1/0; -- Division by zero error
END TRY
BEGIN CATCH
    PRINT 'An error occurred!';
END CATCH;

Working with Cursors

PL/pgSQL: cursors are implicit and can be used right in loops.

FOR row IN SELECT * FROM students LOOP
    RAISE NOTICE 'Student: %', row.name;
END LOOP;

PL/SQL: cursors are explicitly declared structures. For example:

DECLARE
    CURSOR student_cursor IS SELECT * FROM students;
    student_row students%ROWTYPE;
BEGIN
    OPEN student_cursor;
    FETCH student_cursor INTO student_row;
    CLOSE student_cursor;
END;

T-SQL: cursors are declared with the CURSOR keyword.

DECLARE student_cursor CURSOR FOR SELECT name FROM students;
OPEN student_cursor;
FETCH NEXT FROM student_cursor;
CLOSE student_cursor;
DEALLOCATE student_cursor;

Working with Transactions

PL/pgSQL: transactions are managed with BEGIN, COMMIT, ROLLBACK commands.

PL/SQL: transactions are managed the same way with COMMIT, ROLLBACK, and support SAVEPOINT.

T-SQL: adds BEGIN TRANSACTION to mark the start of a transaction.

JSON Support

PL/pgSQL: super powerful JSON handling with JSON and JSONB data types. Example:

SELECT data->>'key' FROM json_table;

PL/SQL: JSON support came later and is a bit less flexible.

T-SQL: really handy JSON handling with JSON_QUERY, JSON_VALUE functions.

When Should You Use PL/pgSQL, PL/SQL, or T-SQL?

PL/pgSQL:

• Obvious choice if your database is PostgreSQL.
• Great for crunching big data sets thanks to powerful data types (JSONB, arrays).
• Open ecosystem, lots of flexibility.

PL/SQL:

• The go-to for Oracle products.
• Rich ecosystem for data work (packages, built-in procedures).

T-SQL:

• Used in Microsoft SQL Server.
• Perfect for working with Microsoft apps and integrating into the Microsoft Azure stack.

Example: The Same Task in PL/pgSQL, PL/SQL, and T-SQL

Task: Count the number of students and return the result

PL/pgSQL:

CREATE FUNCTION count_students() RETURNS INT AS $$
DECLARE
    total INT;
BEGIN
    SELECT COUNT(*) INTO total FROM students;
    RETURN total;
END;
$$ LANGUAGE plpgsql;

PL/SQL:

CREATE OR REPLACE FUNCTION count_students RETURN NUMBER IS
    total NUMBER;
BEGIN
    SELECT COUNT(*) INTO total FROM students;
    RETURN total;
END;

T-SQL:

CREATE FUNCTION count_students()
RETURNS INT
AS
BEGIN
    DECLARE @total INT;
    SELECT @total = COUNT(*) FROM students;
    RETURN @total;
END;

Now you know the basic differences between PL/pgSQL, PL/SQL, and T-SQL. Each language has its own quirks and use cases that make it unique. Which one you pick (and which database you use) always depends on your needs and the specifics of your project.