Editing PostgreSQL (section)

=== Procedural languages ===
Procedural languages allow developers to extend the database with custom [[subroutine]]s (functions), often called ''[[stored procedure]]s''. These functions can be used to build [[database trigger]]s (functions invoked on modification of certain data) and custom data types and [[aggregate function]]s.<ref>{{cite web|url=https://www.postgresql.org/docs/current/server-programming.html|title=Server Programming|access-date=19 May 2019|website=PostgreSQL documentation}}</ref> Procedural languages can also be invoked without defining a function, using a DO command at SQL level.<ref>{{cite web|url=https://www.postgresql.org/docs/current/sql-do.html|title=DO|access-date=19 May 2019|website=PostgreSQL documentation}}</ref>

Languages are divided into two groups: Procedures written in ''safe'' languages are [[Sandbox (computer security)|sandboxed]] and can be safely created and used by any user. Procedures written in ''unsafe'' languages can only be created by [[superuser]]s, because they allow bypassing a database's security restrictions, but can also access sources external to the database. Some languages like Perl provide both safe and unsafe versions.

PostgreSQL has built-in support for three procedural languages:

* Plain SQL (safe). Simpler SQL functions can get [[inline expansion|expanded inline]] into the calling (SQL) query, which saves function call overhead and allows the query optimizer to "see inside" the function.
* Procedural Language/PostgreSQL ([[PL/pgSQL]]) (safe), which resembles Oracle's Procedural Language for SQL ([[PL/SQL]]) procedural language and SQL/Persistent Stored Modules ([[SQL/PSM]]).
* [[C (programming language)|C]] (unsafe), which allows loading one or more custom [[shared library]] into the database. Functions written in C offer the best performance, but bugs in code can crash and potentially corrupt the database. Most built-in functions are written in C.

In addition, PostgreSQL allows procedural languages to be loaded into the database through extensions. Three language extensions are included with PostgreSQL to support [[Perl]], [[Tcl]], and [[Python (programming language)|Python]]. For Python, the current {{nowrap|Python 3}} is used, and the discontinued {{nowrap|Python 2}} is no longer supported as of PostgreSQL 15. Both were supported previously, defaulting to {{nowrap|Python 2}}, while old and new versions couldn't be used in the same session.<ref>{{cite web|url=https://www.postgresql.org/docs/current/plpython.html|title=PL/Python - Python Procedural Language|access-date=October 23, 2022|website=PostgreSQL documentation}}</ref> External projects provide support for many other languages,<ref>{{cite web |date=March 31, 2016 |title=Procedural Languages |publisher=postgresql.org |url=https://www.postgresql.org/docs/current/static/external-pl.html |access-date=April 7, 2016}}</ref> including PL/[[Java (programming language)|Java]], [[JavaScript]] (PL/V8), PL/[[Julia (programming language)|Julia]],<ref name="PL/Julia">{{Cite web|date=2020-03-08|title=PL/Julia extension ( minimal )|url=https://discourse.julialang.org/t/pl-julia-extension-minimal/34232/2|access-date=2021-08-26|website=JuliaLang|language=en}}</ref> PL/[[R (programming language)|R]],<ref>{{Cite web|url=https://github.com/postgres-plr/plr|title=postgres-plr/plr|date=June 17, 2021|via=GitHub}}</ref> PL/[[Ruby (programming language)|Ruby]], and others.