The purpose of this posting is to grasp the internal implementation of PL/pgSQL briefly. Today, we will show the meaning of PLs and introduce source codes of PL/pgSQL first.
Why we use PLs?
Every SQL statement must be executed individually by the database server. So, a client application must send each query to the database server, wait for it to be processed, receive and process the results, and do some computation, then send further queries to the server. All these incur inter-process communication and will also incur network overhead if the client is on a different machine than the database server.
With PLs you can group a block of computation and a series of queries inside the database server, thus having the power of a procedural language and the ease of use of SQL, but with considerable savings of client/server communication overhead.
PLs in PostgreSQL
PLs in PostgreSQL are supported as extensions. This means that if you want to remove a procedural language from your production database because of some security issues, you can simply remove it as a super-user.
Source codes
Source codes of PL/pgSQL are located at `src/pl/plpgsql/src/`. Brief descriptions of some notable files are listed below.
| File | Description |
| pl_comp.c | Compiles a PL/pgSQL code block into a executable function |
| pl_exec.c | Executes the compiled code block |
| pl_funcs.c | Miscellanies for namespace, memory and dump |
| pl_gram.y | PL/pgSQL parser (invoked by the compiler) |
| pl_handler.c | Entry points for executing PL/pgSQL procedures |
| pl_scanner.c | Scanner for PL/pgSQL (uses PostgreSQL core scanner) |
We’re going to associate the files with the phases of procedure execution in order.
Handler (pl_handler.c)
Every extension has a function which initializes itself. The name of the function is `_PG_init()`. It is called by PostgreSQL when an extension is dynamically loaded. This file has the function for PL/pgSQL extension. And also, this file has entry points for executing PL/pgSQL procedures. These entry points are called when PL/pgSQL procedures in SQLs are called. Since PL/pgSQL is a built-in extension, it is automatically installed in the `template1` database when `initdb` which uses `postgres.bki` generated by `genbki.pl`. See also BKI and ` pg_pltemplate ` for more information.
Compiler (pl_comp.c)A stored procedure written in PL/pgSQL must be compiled when it is called at first. The compilation process in here is somewhat simple. It parses a function body(PL/pgSQL code block) through the PL/pgSQL parser(pl_gram.y). The parser builds a parse tree which is also executable. The parse tree consists of `PLpgSQL_stmt_xxx` structures. Compiled procedures are cached in the per process “PLpgSQL function cache”.
Executor (pl_exec.c)
The executor executes compiled PL/pgSQL procedures. It uses SPI(Server Programming Interface) to evaluate expressions and execute SQL queries. More precisely, expressions are evaluated through SELECT queries (e.g. `SELECT 1 + 1`). These queries are cached in the current process like prepared statements. And `RETURN NEXT` statement stores results to a temporal tuple store which stores data in memory. Any data beyond this amount of memory is paged to disk. For more information, see `tuplestore_begin_heap()`. Also, it supports variable number of arguments and polymorphic types.
Conclusion
In this posting, we introduced what is PL/pgSQL and the files related to PL/pgSQL implementation briefly. Next time, we will continue from Handler in depth which contains how PL/pgSQL procedures are called and more.
About this page
Last reviewed October 30, 2026 by the Nine Bits editors. Read our editorial policy or report an error.