PLpgSQL

PL/pgSQL is PostgreSQL's native procedural language for writing server-side functions and trigger procedures. It extends SQL with a procedural syntax, enabling complex data processing inside the database while preserving the benefits of relational operations.

The language provides features such as variable declarations, control structures (IF, CASE, LOOP, FOR, WHILE), and exception handling (EXCEPTION WHEN ... THEN). It supports dynamic SQL through the EXECUTE statement, as well as cursors and the use of arrays and composite types. Functions can return scalar values, rows, or setof records, allowing a wide range of result forms from a single implementation.

In PostgreSQL, PL/pgSQL functions are stored in the database and can be invoked from SQL statements, used in triggers, or composed in views and other functions. They are declared with CREATE FUNCTION ... RETURNS ... AS $$ ... $$ LANGUAGE plpgsql. The language is designed for writing business logic, data validation, and batch processing, delivering procedural capabilities with the efficiency of operating close to the data.

Example: CREATE FUNCTION increment(n integer) RETURNS integer AS $$ BEGIN RETURN n + 1; END; $$ LANGUAGE plpgsql;

PL/pgSQL is widely used in PostgreSQL deployments and complements other procedural languages available in the ecosystem (such as PL/Perl and PL/Python). It runs on the server under the current user’s security context and integrates with PostgreSQL’s transactional model, making it a common choice for encapsulating logic that should execute reliably within the database.