PL/pgSQL

PL/pgSQL ("Procedural Language/PostgreSQL") is PostgreSQL's native procedural language, used to write functions, procedures, and trigger bodies. Plain SQL is declarative — a single statement describing what result you want. PL/pgSQL adds real procedural control flow on top: variables, IF/CASE branching, LOOP/WHILE/FOR loops, and error handling — the kind of logic plain SQL simply has no syntax for.

Basic structure

A PL/pgSQL function body is organized into an optional DECLARE section for local variables, followed by a BEGIN ... END block containing the executable statements:

SQL

CREATE FUNCTION greet(name text)
RETURNS text
LANGUAGE plpgsql
AS $$
DECLARE
  greeting text;
BEGIN
  greeting := 'Hello, ' || name || '!';
  RETURN greeting;
END;
$$;

SQL

SELECT greet('World');

greet
--------------
Hello, World!

Dollar-quoting: $$ ... $$

Notice the function body is wrapped in $$ ... $$ rather than ordinary single quotes. This is PostgreSQL's dollar-quoting syntax, and it exists specifically to solve a real annoyance: a function body is itself a string literal, and that body is almost always full of single-quoted SQL string literals of its own (like 'Hello, ' above). Without dollar-quoting, every one of those inner quotes would have to be doubled up and escaped, which gets unreadable fast in anything beyond a trivial function.

Note

With $$ ... $$, everything between the two $$ markers is taken literally — no escaping needed, no matter how many single quotes appear inside. You can also tag the delimiters, e.g. $body$ ... $body$, which is useful when a function body needs to contain a literal $$ itself (for example, when one function dynamically builds and executes another piece of SQL). This is a genuinely convenient feature that most other SQL dialects don't offer.

A worked example with control flow

Here PL/pgSQL's procedural features actually earn their keep — branching logic that plain SQL expressions can't express directly:

SQL

CREATE FUNCTION grade_label(score integer)
RETURNS text
LANGUAGE plpgsql
AS $$
BEGIN
  IF score >= 90 THEN
    RETURN 'A';
  ELSIF score >= 80 THEN
    RETURN 'B';
  ELSIF score >= 70 THEN
    RETURN 'C';
  ELSE
    RETURN 'F';
  END IF;
END;
$$;

SELECT grade_label(87);

grade_label
-------------
B

Why PL/pgSQL is the default choice

PL/pgSQL is built into PostgreSQL and tightly integrated with SQL — you can mix plain SQL statements and procedural logic freely, which is why it's by far the most common language for PostgreSQL functions, procedures, and triggers.

Note

PostgreSQL supports other procedural languages too — PL/Python, PL/Perl, PL/Tcl, and more — but each requires installing and enabling an extension first (CREATE EXTENSION plpython3u, for example), and they're typically reached for only when a task needs a specific external library or capability that PL/pgSQL doesn't provide. For everyday functions and triggers, PL/pgSQL is the natural default.

PL/pgSQL adds variables, branching, loops, and error handling on top of plain SQL.
A function body is delimited with $$ ... $$ (dollar-quoting) instead of single quotes, avoiding escaping headaches.
The basic shape is DECLARE (optional) then BEGIN ... END.
Other procedural languages exist as extensions, but PL/pgSQL is the built-in, default choice.