9bNine Bits

PL/pgSQL Internals: How PostgreSQL Compiles and Executes Procedures

PL/pgSQL is PostgreSQL's built-in procedural language. Each function is parsed into an in-memory tree the first time it runs in a session, cached for reuse, and then interpreted statement by statement, with embedded SQL handed off to the server's SPI interface for its own parsing and plan caching. That split between PL/pgSQL control flow and SPI-managed SQL explains both its performance profile and why some errors only appear at runtime.
PL/pgSQL Internals: How PostgreSQL Compiles and Executes Procedures

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.

 

Common questions

Is PL/pgSQL compiled or interpreted?

It is interpreted. PostgreSQL parses the function body into a tree of statement nodes on first call within a session and caches that tree, but it does not compile the function to machine code or bytecode. The interpreter walks the cached tree on every call.

Why didn't CREATE FUNCTION catch an error that only shows up when I call the function?

CREATE FUNCTION only checks the PL/pgSQL syntax, not the embedded SQL inside each branch. Type resolution and validation of a given SQL statement happen the first time that specific statement actually executes, so bugs in rarely used branches can stay hidden until they run.

Does calling a PL/pgSQL function repeatedly reparse the SQL inside it?

No. Both the PL/pgSQL parse tree and the plans for its embedded SQL statements are cached per session through SPI after the first call. Subsequent calls in the same session reuse those cached results rather than reparsing or replanning from scratch.

When should I avoid putting logic in PL/pgSQL?

PL/pgSQL is a good fit for logic that needs to run close to the data and avoid network round trips, such as validation, multi-step updates, or trigger logic. For CPU-intensive computation or complex algorithms, application code or a compiled extension is usually a better fit.

Sources

  1. PostgreSQL Documentation: PL/pgSQL
  2. PostgreSQL Documentation: Server Programming Interface (SPI)
  3. plpgsql_check (open-source static checker for PL/pgSQL)

About this page

Last reviewed October 30, 2026 by the Nine Bits editors. Read our editorial policy or report an error.