In PostgreSQL, how do you choose between LANGUAGE sql, LANGUAGE plpgsql and an untrusted language such as plpython3u for a new server-side function?
answer
- what the planner can see into
- RETURN and BEGIN ATOMIC bind names early
- control flow and error handling
- untrusted means superuser-only, OS access
- plperl versus plperlu
basics
~20 sUse LANGUAGE sql when the logic is a query or expression, since it can be inlined and a RETURN or BEGIN ATOMIC body tracks dependencies; plpgsql for variables, loops and error handling; an untrusted language only for work SQL cannot do.
solid answer
~50 sStart with `LANGUAGE sql`: a body that is a query or an expression can be inlined into the calling query, and the standard `RETURN expr` or `BEGIN ATOMIC ... END` form is parsed at `CREATE` time, binds object names then, and records dependencies so `DROP ... CASCADE` behaves. Move to `plpgsql` when you need variables, branching, loops, dynamic SQL or exception handling; its body is syntax-checked at creation, its statements are planned when first run, and it is never inlined. Untrusted languages such as `plpython3u`, `plperlu` and `pltclu` give unrestricted access as the operating-system user running the server, so only superusers can create functions in them; use them for work SQL cannot do, keep them small, and treat them as privileged code. Trusted `plperl` and `pltcl` are open to ordinary roles that hold `USAGE` on the language.
code
sql · 26 lines-- Pure expression: inlinable, names bound at CREATE
CREATE FUNCTION fee_for(amount numeric)
RETURNS numeric
LANGUAGE sql IMMUTABLE PARALLEL SAFE
RETURN greatest(round(amount * 0.015, 2), 0.50);
-- Control flow and a meaningful error: plpgsql
CREATE FUNCTION withdraw(p_account_id bigint, p_amount numeric)
RETURNS numeric
LANGUAGE plpgsql
AS $$
DECLARE
new_balance numeric;
BEGIN
UPDATE account SET balance = balance - p_amount
WHERE id = p_account_id AND balance >= p_amount
RETURNING balance INTO new_balance;
IF NOT FOUND THEN
RAISE EXCEPTION 'insufficient funds on account %', p_account_id;
END IF;
RETURN new_balance;
END;
$$;
-- Which installed languages are trusted?
SELECT lanname, lanpltrusted FROM pg_language;go deeper
Recall the three options: plain SQL functions, PL/pgSQL for procedural logic, and untrusted languages ending in u that only superusers may use.
Explain what each choice costs: SQL can be inlined and binds names early in its standard body form, PL/pgSQL adds control flow but is opaque to the planner, and untrusted code has no sandbox.
Show judgement about privileged code: who may create untrusted functions, how their owners are controlled, and why network calls from inside a transaction usually move to workers.
Set a team policy on server-side languages, weighing operational risk, packaging of language extensions across upgrades, and reviewability against the convenience of code next to the data.
## Three families of language A PostgreSQL function names its implementation language with `LANGUAGE`. The choice decides what the planner can see, who may create the function, and what the code can reach. | Language | Who can create functions | How the body runs | Main limits | |---|---|---|---| | `sql` | any role with `USAGE`, granted to `PUBLIC` by default | parsed and planned by the core; can be inlined | no variables, loops or exception handling | | `plpgsql` | any role with `USAGE`, granted to `PUBLIC` by default | interpreted; each statement planned on first use | opaque to the planner; interpreter overhead per call | | `plperl`, `pltcl` (trusted) | any role with `USAGE`, granted to `PUBLIC` by default | interpreter restricted from the file system and server internals | no outside libraries or system access | | `plpython3u`, `plperlu`, `pltclu` (untrusted) | superusers only | full language, no sandbox | runs with the server's operating-system privileges | | `c` | superusers only | compiled shared library | a bug can take down the backend | Whether a language is trusted is recorded in `pg_language.lanpltrusted`. `GRANT USAGE` on an untrusted language is refused outright, because only superusers may use it. Installing a language with `CREATE EXTENSION` is covered under extensions. ## LANGUAGE sql: the default choice - **Inlining.** A simple SQL function can be substituted into the calling query, so the planner optimises the function's query together with the caller's. No procedural language gets this. - **Two body forms.** The dollar-quoted string form is parsed when the function runs. The standard `RETURN expression` and `BEGIN ATOMIC ... END` forms are parsed at creation: object names are bound then, so a later change to `search_path` cannot redirect them, and dependencies are recorded so `DROP ... CASCADE` handles the function instead of leaving it dangling. The price is that the creation-time form cannot use polymorphic argument types such as `anyelement`. - **Limits.** No variables, no `IF`, no loops, no exception handling, and no transaction statements. ## LANGUAGE plpgsql: when procedural logic earns its keep PL/pgSQL is installed in every database by default and suits logic that needs state and decisions: checking a balance before a withdrawal, raising a meaningful error, looping over a cursor, building dynamic SQL. Its body is syntax-checked at `CREATE` time when `check_function_bodies` is on, which is the default; table and column references are resolved when each statement first runs. It is never inlined, so a set-returning PL/pgSQL function is a black box to the planner. The statements themselves, and how their plans are cached, belong to the PL/pgSQL topic. ## Untrusted languages: what untrusted means 1. **No sandbox.** A `plpython3u` function can open files, start processes and make network calls with the privileges of the operating-system account that runs PostgreSQL. 2. **Superuser-only creation.** Only superusers can create functions in an untrusted language, though they can grant others `EXECUTE` on the result. 3. **Privileged code.** Such a function is effectively a door into the server host; review it like code that runs as the database administrator, and wrap it with the same care as a `SECURITY DEFINER` function. 4. **Operational cost.** The language and its libraries must be present on every server, including standbys and upgrade targets. ## A decision order 1. Can it be one query or expression? Write `LANGUAGE sql`, preferably in the `RETURN` or `BEGIN ATOMIC` form. 2. Does it need variables, branches, loops or error handling? Write `plpgsql`. 3. Does it need a library or system access SQL cannot provide? Ask first whether an application worker should do it; if it truly belongs in the server, use an untrusted language, created and owned by a superuser, and keep it minimal. In a banking app, a fee calculation is plain SQL, a withdrawal with a balance check is PL/pgSQL, and a call to an external fraud API belongs in an application worker, not in a transaction holding row locks. ## Common mistakes - Writing a one-line lookup in PL/pgSQL out of habit, which hides it from the planner and forfeits inlining. - Assuming a trusted language is harmless in every respect: it cannot reach the file system, but it still runs arbitrary computation inside a backend and can hold locks for as long as it runs. - Treating untrusted as a quality judgement. The word describes the security model, no sandbox, not the stability of the language.
- Why might a team prefer a BEGIN ATOMIC body over a dollar-quoted string for a LANGUAGE sql function?The `BEGIN ATOMIC` and `RETURN` forms are parsed when the function is created, so object names are bound then rather than through the caller's `search_path`, and dependencies are recorded so `DROP ... CASCADE` handles the function instead of leaving it dangling. The cost is that polymorphic argument types and anything unresolvable at creation time are not supported.
- A developer asks for plpython3u so a function can call an external HTTP API. What do you weigh?Only a superuser can create the function, and its code runs with the server's operating-system privileges, so a bug becomes host access. A blocking network call also holds the backend, and any locks its transaction took, for the whole call. The usual better design records the request in a table and lets an application worker make the call.
- PL/pgSQL functions are inlined into the calling query like SQL functions.
- Untrusted means the language is unstable, so its functions crash more often.
- Any role granted USAGE on plpython3u can create functions in it.
- A RETURN-form SQL function re-resolves its table names through the caller's search_path.
- PL/pgSQL is always faster than plain SQL because it is compiled.