Skip to content
Tutorials Center
My learning

Topics

Lesson 54 of 90 ยท Functions, PL/pgSQL and Triggers

Other Procedural Languages and Aggregates

Survey PostgreSQL's other procedural languages (PL/Python, PL/Perl), write custom aggregates and operators, meet foreign data wrappers, and decide what logic belongs in the database.

PL/pgSQL is the language most people use for database code, but it is not the only one. PostgreSQL is built so that the language of a function is a plug-in: SQL and PL/pgSQL come built in, and others (Python, Perl, Tcl, JavaScript, R and more) can be installed as extensions. You can also teach PostgreSQL new tricks beyond functions: custom aggregates that fold many rows into one value, operators that give your own types a natural syntax, and foreign data wrappers that let you query other systems as if they were tables.

This lesson surveys those tools and finishes with the question that matters most: how much logic should live in the database at all?

What runs live here. The in-browser editor uses SQLite, which has none of these features, so every PostgreSQL block below is read-only. The live examples show the ideas underneath, such as how an aggregate folds rows into a state, using ordinary SQL.

How languages plug in

Every function names its language: LANGUAGE sql, LANGUAGE plpgsql, LANGUAGE plpython3u. A language is available once its extension is installed in the database:

SQL
CREATE EXTENSION plpython3u;

Languages come in two flavours, marked by a final letter:

  • Trusted (no u), such as plpgsql: the code is restricted so that it cannot touch the server's file system or network. Ordinary users with the USAGE privilege may write functions in it.
  • Untrusted (ends in u), such as plpython3u or plperlu: the code runs with the full power of the language, including opening files and sockets as the operating-system user that runs PostgreSQL. Only superusers can create functions in an untrusted language, and managed cloud services often forbid or restrict them.

That distinction is a security boundary. A function in an untrusted language can read any file the database server can read, so treat such functions like code deployed to the server, not like application data.

PL/Python

PL/Python lets you write the function body in Python, with the whole Python standard library at hand. Arguments arrive as Python variables, and the return value is converted back to a PostgreSQL type:

SQL
CREATE FUNCTION slugify(title text)
RETURNS text
LANGUAGE plpython3u
IMMUTABLE
AS $$
  import re, unicodedata
  s = unicodedata.normalize('NFKD', title)
  s = s.encode('ascii', 'ignore').decode('ascii').lower()
  return re.sub(r'[^a-z0-9]+', '-', s).strip('-')
$$;

SELECT slugify('Crรจme Brรปlรฉe: A Recipe!');   -- creme-brulee-a-recipe

Inside the function, the plpy module runs queries (plpy.execute("SELECT ...")) and logs messages (plpy.notice(...)). Python lists become PostgreSQL arrays and back again, and a function can return a composite row or a set of rows with yield.

Treat PL/Python as a bridge, not a default. It shines when a task needs a library or an algorithm that is awkward in SQL: text normalization, a statistical routine, a call into a machine-learning package. It also carries costs: the interpreter starts for every connection that uses it, it holds the Python process memory inside the database backend, a slow or crashing function affects the server, and managed services seldom allow it. For anything that needs the network or heavy computation, a separate application service is usually the better place.

PL/Perl works the same way (plperl is trusted and restricted, plperlu is untrusted) and is a good fit for text-heavy functions that lean on regular expressions. PL/Tcl exists in both flavours too. Third-party languages such as PL/V8 (JavaScript) and PL/R exist as extensions, but check that your hosting provider supports them before designing around one.

Custom aggregates

You already use aggregates like sum, avg and count. PostgreSQL lets you define your own with CREATE AGGREGATE. An aggregate is a small machine with three parts:

  1. A state value, with an initial value (INITCOND).
  2. A state transition function (SFUNC) called once per input row, which takes the current state plus the row's value and returns the new state.
  3. An optional final function (FINALFUNC) which turns the last state into the result.

Take a weighted average. The state is a pair: the running sum of value * weight and the running sum of weight. The final function divides one by the other:

SQL
CREATE TYPE wavg_state AS (weighted_sum numeric, total_weight numeric);

CREATE FUNCTION wavg_step(s wavg_state, val numeric, w numeric)
RETURNS wavg_state
LANGUAGE sql
IMMUTABLE
AS $$
  SELECT (COALESCE(s.weighted_sum, 0) + val * w,
          COALESCE(s.total_weight, 0) + w)::wavg_state;
$$;

CREATE FUNCTION wavg_final(s wavg_state)
RETURNS numeric
LANGUAGE sql
IMMUTABLE
AS $$
  SELECT s.weighted_sum / NULLIF(s.total_weight, 0);
$$;

CREATE AGGREGATE weighted_avg(numeric, numeric) (
  SFUNC     = wavg_step,
  STYPE     = wavg_state,
  FINALFUNC = wavg_final
);

SELECT product_id, weighted_avg(rating, helpful_votes)
FROM reviews
GROUP BY product_id;

Once defined, weighted_avg works with GROUP BY, FILTER and even as a window function with OVER. Two options make aggregates faster. COMBINEFUNC merges two partial states, which lets the planner compute the aggregate in parallel workers. MSFUNC and MINVFUNC define an inverse transition so that a moving window can add and remove rows incrementally instead of recomputing.

Here is the same weighted average as plain SQL, live. It also lets you see the two sums that make up the aggregate's state:

SQL The state behind a weighted average

Press Run (or Ctrl + Enter) to see the result here.

Product 1's plain average is 4.0, but the weighted one is 4.76, because the five-star review has by far the most helpful votes. weighted_sum and total_weight are the two components of the aggregate state; the division is the final function.

Many "custom aggregates" are unnecessary because the built-ins already cover them: string_agg, array_agg, jsonb_agg, percentile_cont, mode() and bool_and handle most needs. Write your own only when the folding logic is genuinely new.

The state-passing idea is the same as a running calculation, so you can watch one step by step with a window function:

SQL Folding rows one at a time

Press Run (or Ctrl + Enter) to see the result here.

The state_after_row column is what a transition function produces after each input: 50, 80, 60, 160. The final aggregate value is simply the last state.

Custom operators

PostgreSQL operators are just functions with infix syntax. You can create your own, which is most useful for custom types:

SQL
CREATE FUNCTION text_overlap_len(a text, b text) RETURNS int
LANGUAGE sql IMMUTABLE AS $$
  SELECT length(a) - length(replace(a, b, ''));
$$;

CREATE OPERATOR === (
  LEFTARG  = text,
  RIGHTARG = text,
  FUNCTION = text_overlap_len
);

Real operator definitions add hints (COMMUTATOR, NEGATOR, RESTRICT, JOIN) so the planner can reorder and estimate them, and an operator class connects an operator to an index method such as B-tree or GiST. That is how pg_trgm and range types get fast searches. Most application developers use operators defined by extensions and rarely write their own, so treat this as background knowledge.

Foreign data wrappers, briefly

A foreign data wrapper (FDW) lets PostgreSQL query data that lives elsewhere, using the same SQL:

SQL
CREATE EXTENSION postgres_fdw;

CREATE SERVER reporting_db
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host 'reports.internal', dbname 'reports');

CREATE USER MAPPING FOR app_user
  SERVER reporting_db
  OPTIONS (user 'reader', password 'not-a-real-password');

CREATE FOREIGN TABLE remote_sales (
  id int, sold_on date, amount numeric
) SERVER reporting_db OPTIONS (schema_name 'public', table_name 'sales');

SELECT sold_on, sum(amount) FROM remote_sales GROUP BY sold_on;

The foreign table is a local name for a remote table, and the planner can push filters and sometimes aggregates to the remote side. Wrappers exist for other PostgreSQL servers, MySQL, SQLite, files (file_fdw), Redis, MongoDB and many more. A later lesson, Foreign Data Wrappers and Cross-Database Access, covers pushdown, performance and security. For now, remember the shape: server, user mapping, foreign table.

Ways to extend PostgreSQL

Languages PL/Python and PL/Perl bring whole libraries.
Aggregates A state, a step function and a final function.
Operators Functions with infix syntax and index support.
Wrappers Query other systems like local tables.
1 / 2

When does logic belong in the database?

With triggers, functions and procedures at hand, the temptation is to put everything in the database. A balanced view helps.

Good fits for database logic:

  • Invariants that must hold for every writer. Constraints, and the occasional trigger, protect data from every application, script and manual fix.
  • Set-based work close to the data. Aggregations and bulk updates are fastest inside the server, with no network round trips.
  • Small, reusable queries. A STABLE SQL function or a view gives many applications one definition.
  • Auditing and bookkeeping that must be atomic with the change.

Poor fits:

  • Complex business workflows with many branches. They are harder to test, version, review and debug as stored code than as application code with a normal test suite and debugger.
  • Anything that calls other services (email, payments, HTTP). A transaction can roll back after the call has already happened. Use an outbox table and a worker instead.
  • Logic that scales out. The database is the hardest component to scale. Heavy CPU work in functions competes with queries for the same machine.
  • Code your team cannot review in its usual workflow. Keep database code in migrations under version control.

A practical rule: let the database own data integrity and set-based data work, and let the application own decisions and side effects. Constraints first, plain SQL second, small functions third, and procedural code last.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What distinguishes an untrusted language such as plpython3u from a trusted one?

    Untrusted languages are not sandboxed. A function can read files or open sockets as the PostgreSQL OS user, so creating one is restricted to superusers.
  2. 2. Which parts make up a custom aggregate defined with CREATE AGGREGATE?

    The transition function folds each row into the state, and the optional final function turns the last state into the result. COMBINEFUNC and the moving-aggregate functions are optional optimizations.
  3. 3. In the foreign data wrapper setup, which object stores the connection details of the remote system?

    CREATE SERVER describes where the remote system is, CREATE USER MAPPING holds the credentials, and CREATE FOREIGN TABLE exposes a remote table locally.
  4. 4. Which task is the WEAKEST fit for logic inside the database?

    A trigger that calls an external service can send the email and then see the transaction roll back. Write an outbox row instead and let a worker send the email after the commit.

Summary

  • Languages are plug-ins: trusted ones (PL/pgSQL) are sandboxed, untrusted ones (plpython3u, plperlu) have full server access and need a superuser.
  • PL/Python and PL/Perl are bridges to existing libraries, best used sparingly and not assumed to exist on managed hosting.
  • A custom aggregate is a state, a transition function and an optional final function; COMBINEFUNC enables parallel aggregation.
  • Operators are functions with infix syntax, and operator classes connect them to indexes.
  • Foreign data wrappers need a server, a user mapping and foreign tables.
  • Keep data integrity and set-based work in the database, and keep decisions and side effects in the application.
  • The live examples reproduced the aggregate state and a running fold in plain SQLite.

Next: Extensions, where you will learn how PostgreSQL adds features such as these with CREATE EXTENSION.