How to Use the PL/PHP Language in PostgreSQL

PostgreSQL is famous for one thing that a lot of other databases simply don’t offer: the ability to write stored procedures and functions in more than one programming language. Most people know about PL/pgSQL because it ships with PostgreSQL by default, but far fewer people know that I can actually write database functions in PHP using an extension called PL/PHP. If I already have a team of PHP developers, or if my application layer is built entirely in PHP, this procedural language lets me push logic directly into the database using syntax I already know.

In this guide, I’m going to walk through what PL/PHP actually is, how to install it, how to write functions with it, what parameters and return types look like, and where it genuinely makes sense to use it in a real production system. I’ll also cover troubleshooting issues I’ve run into personally, along with a set of best practices I’d recommend to anyone considering it.

What Is PL/PHP?

PL/PHP is a procedural language extension for PostgreSQL that allows me to write user-defined functions and trigger functions using the PHP scripting language, executed inside the PostgreSQL server process. Instead of writing a function body in SQL or PL/pgSQL syntax, I write it as a block of PHP code, and PostgreSQL hands off execution to an embedded PHP interpreter.

This is conceptually similar to PL/Python or PL/Perl — PostgreSQL supports a whole family of these “PL” (Procedural Language) extensions so that developers aren’t locked into a single syntax for stored logic. PL/PHP specifically appeals to teams whose primary web stack is PHP, since it lets database-side logic feel consistent with the rest of the codebase.

It’s worth being upfront: PL/PHP is not part of the default PostgreSQL distribution, and unlike PL/pgSQL or even PL/Python, it isn’t nearly as actively maintained. I still think it’s worth understanding if I’m inheriting a legacy system that uses it, or if my organization has a strong PHP-only mandate, but for greenfield projects I’d weigh it carefully against PL/pgSQL or PL/Python, which have much larger communities and more predictable long-term support.

Why Use PL/PHP Instead of PL/pgSQL?

There are a handful of scenarios where I’d reach for PL/PHP over the built-in PL/pgSQL:

• Existing PHP expertise. If my entire engineering team writes PHP daily and rarely touches PL/pgSQL syntax, letting them write trigger functions or stored procedures in familiar syntax reduces onboarding friction.
• String and text manipulation. PHP has an enormous standard library for string processing, regular expressions, and array manipulation that can feel more natural than PL/pgSQL’s more limited built-in functions.
• Reusing existing PHP libraries. In some setups, I can leverage PHP libraries already used by the application layer, cutting down on duplicated logic.
• Complex procedural logic. For functions that need loops, conditionals, and multi-step processing that goes beyond a simple SQL query, PHP’s control structures can feel more readable to PHP-first teams.

That said, PL/pgSQL will almost always be faster for logic that’s tightly coupled to SQL operations, since it’s designed from the ground up to interact with the query planner and native PostgreSQL types. I only reach for PL/PHP when there’s a genuine reason to prefer PHP syntax.

Installing PL/PHP

Before I can create any PL/PHP functions, the extension needs to be compiled and installed on the PostgreSQL server. Unlike PL/pgSQL, which is baked in, PL/PHP typically needs to be built from source against both the PostgreSQL server headers and the PHP embed SDK.

On a Debian/Ubuntu-based system, the general workflow looks like this:

# Install PostgreSQL server development headers
sudo apt-get install postgresql-server-dev-16

# Install PHP development headers and the embed SAPI
sudo apt-get install php-dev libphp-embed

# Download and build PL/PHP from source
git clone https://github.com/postgresql-php/plphp.git
cd plphp
make USE_PGXS=1
sudo make USE_PGXS=1 install

Package availability varies significantly by distribution and PostgreSQL version, so I always double-check whether a prebuilt package exists for my specific OS and PostgreSQL version before building from source. Once installed, I create the language extension inside the target database:

CREATE EXTENSION plphp;

If I need untrusted mode (which allows filesystem and network access from inside functions), I create it explicitly:

CREATE LANGUAGE plphpu;

I only enable the untrusted variant when absolutely necessary, since it removes the sandboxing that protects the rest of the database from arbitrary code execution by non-superuser roles.

Basic Syntax of a PL/PHP Function

A PL/PHP function looks structurally similar to any other PostgreSQL function definition, except the function body is a block of PHP code:

CREATE OR REPLACE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER
AS $$
    return $a + $b;
$$ LANGUAGE plphp;

A few things to notice here:

• Function parameters (a and b) are automatically available inside the PHP code as variables prefixed with $ — so a becomes $a.
• The return statement inside the PHP block determines what value is passed back to PostgreSQL.
• The LANGUAGE plphp clause tells PostgreSQL which procedural language handler to use to execute the function body.

I can call this function exactly like any native SQL function:

SELECT add_numbers(4, 7);

Working with Parameters and Return Types

PL/PHP supports scalar types (integers, text, booleans, numeric values) as well as more complex types like arrays and composite types, though with some caveats around how PostgreSQL types map to PHP’s native types.

Scalar Parameters

CREATE OR REPLACE FUNCTION greet_user(name TEXT)
RETURNS TEXT
AS $$
    return "Hello, " . $name . "! Welcome to the database.";
$$ LANGUAGE plphp;

SELECT greet_user('Sarah');
-- Hello, Sarah! Welcome to the database.

Returning Sets

PL/PHP functions can return sets of rows by declaring RETURNS SETOF and returning an array of associative arrays:

CREATE OR REPLACE FUNCTION list_even_numbers(max_val INTEGER)
RETURNS SETOF INTEGER
AS $$
    $result = array();
    for ($i = 0; $i <= $max_val; $i += 2) {
        $result[] = $i;
    }
    return $result;
$$ LANGUAGE plphp;

SELECT * FROM list_even_numbers(10);

Returning Composite Types

If I need to return multiple columns, I first define a composite type or use RETURNS TABLE:

CREATE OR REPLACE FUNCTION get_user_info(user_id INTEGER)
RETURNS TABLE(id INTEGER, full_name TEXT, is_active BOOLEAN)
AS $$
    $row = array(
        "id" => $user_id,
        "full_name" => "Jane Doe",
        "is_active" => true
    );
    return array($row);
$$ LANGUAGE plphp;

Practical Example: Trigger Functions in PL/PHP

One of the more common real-world use cases is writing trigger functions. Suppose I want to automatically lowercase and trim an email address before it’s inserted into a users table:

CREATE OR REPLACE FUNCTION normalize_email()
RETURNS trigger
AS $$
    $NEW["email"] = strtolower(trim($NEW["email"]));
    return $NEW;
$$ LANGUAGE plphp;

CREATE TRIGGER trg_normalize_email
BEFORE INSERT OR UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION normalize_email();

This is a good showcase of why some teams like PL/PHP — the strtolower() and trim() functions are things PHP developers already know by heart, and the logic reads naturally without needing to learn PL/pgSQL’s lower() and trim() equivalents (which, admittedly, aren’t hard either, but the point stands for more complex string operations).

Accessing the Database from Within PL/PHP

PL/PHP provides functions to run queries against the same database connection, which is useful when a function needs to look up related data:

CREATE OR REPLACE FUNCTION get_order_total(order_id INTEGER)
RETURNS NUMERIC
AS $$
    $sql = "SELECT SUM(price * quantity) AS total FROM order_items WHERE order_id = " . intval($order_id);
    $result = spi_exec_query($sql);
    $row = $result["rows"][0];
    return $row["total"];
$$ LANGUAGE plphp;

I want to flag something important here: whenever I’m building a query string using PHP concatenation, I have to be extremely cautious about SQL injection, even inside the database itself. PL/PHP provides spi_prepare() and spi_exec_prepared() for parameterized queries, and I always prefer these over raw string concatenation, exactly the same way I would in application-layer PHP code with PDO or mysqli.

CREATE OR REPLACE FUNCTION get_order_total_safe(order_id INTEGER)
RETURNS NUMERIC
AS $$
    $plan = spi_prepare("SELECT SUM(price * quantity) AS total FROM order_items WHERE order_id = $1", array("int4"));
    $result = spi_exec_prepared($plan, array($order_id));
    $row = $result["rows"][0];
    return $row["total"];
$$ LANGUAGE plphp;

Common Use Cases

In practice, I’ve seen PL/PHP used for:

1. Data validation and normalization triggers — cleaning up phone numbers, emails, or addresses before they’re stored.
2. Complex string transformations — templating, formatting, or parsing text using PHP’s rich string function library.
3. Legacy system bridging — organizations that migrated business logic from a PHP application layer directly into the database to enforce consistency across multiple client applications.
4. Custom aggregate or computed columns — where the calculation logic is easier to express procedurally than as a single SQL expression.

Troubleshooting Common Issues

“could not load library” error on CREATE EXTENSION This almost always means the PL/PHP shared library wasn’t built against the exact PostgreSQL version running on the server. I check the output of pg_config --version and make sure I built PL/PHP using headers from that same version.

Segmentation faults during function execution This is one of the most common complaints with PL/PHP historically, largely because embedding a full PHP interpreter inside a long-running server process like PostgreSQL introduces memory management complexity that doesn’t exist for lighter procedural languages. If I hit this, I check PHP extension compatibility first — some PHP extensions aren’t safe to load inside an embedded SAPI context. I usually start with a minimal php.ini and only add extensions I’ve explicitly tested.

Function performs well in isolation but degrades under concurrent load Because PL/PHP boots a PHP interpreter instance tied to the backend process, heavy concurrent use can add meaningful overhead compared to PL/pgSQL. If I’m seeing this, I profile with EXPLAIN ANALYZE combined with server-side logging to see if the bottleneck is really the PHP interpreter startup cost or something else, like a missing index the function’s internal query depends on.

Permission denied errors when using plphpu Untrusted languages in PostgreSQL are restricted to superusers by design, since they allow filesystem and network access. I either grant USAGE carefully through a wrapper function owned by a superuser, or avoid untrusted mode entirely if I can rewrite the logic using only trusted plphp.

Best Practices

• Prefer PL/pgSQL for pure SQL-heavy logic. If a function is 90% SQL and 10% control flow, PL/pgSQL will usually be simpler and faster.
• Always use parameterized queries. Never concatenate raw user input into spi_exec_query() calls.
• Keep functions small and focused. Long, complex PHP blocks inside a database function become hard to test, debug, and version-control effectively.
• Version control your function definitions. I keep every CREATE OR REPLACE FUNCTION statement in a migrations folder alongside the rest of my schema so changes are tracked in git, not just applied ad hoc against production.
• Avoid business logic bloat inside the database. Just because I can put logic in PL/PHP doesn’t mean I should push my entire application layer into the database. I reserve it for logic that specifically benefits from living close to the data — validation, normalization, and lightweight computed values.
• Test extension compatibility before every major PostgreSQL upgrade. Since PL/PHP isn’t part of PostgreSQL core, compatibility with new major versions isn’t guaranteed on release day. I always test in staging first.
• Monitor for memory leaks in long-running sessions. Embedded interpreters can behave differently than their standalone counterparts, so I keep an eye on backend process memory usage over time, especially under connection pooling.

Comparing PL/PHP to Other Procedural Languages

When I’m deciding whether PL/PHP is really the right call, I find it helpful to compare it side by side against the alternatives available in PostgreSQL.

Against PL/pgSQL, PL/PHP loses on raw performance for SQL-heavy logic, since PL/pgSQL is natively integrated with PostgreSQL’s type system and doesn’t pay any interpreter marshaling cost. But PL/PHP wins on developer familiarity if the team is PHP-first, and it wins on string-processing ergonomics for anything beyond simple text manipulation.

Against PL/Python, the two are closer in spirit — both are general-purpose scripting languages embedded in the database — but PL/Python has a noticeably larger and more active community, better documentation, and a richer standard library for things like JSON handling, statistics, and date arithmetic. If I weren’t constrained by an existing PHP codebase, I’d lean toward PL/Python for new development almost every time.

Against PL/Perl, PL/PHP’s advantage is mainly familiarity for web development teams, since Perl’s syntax has fallen out of common use for most developers under 40. PL/Perl still edges out PL/PHP for raw regular expression power, but for general procedural logic, PHP’s syntax tends to read more naturally to a broader audience today.

Security Considerations

Because PL/PHP embeds a full scripting language interpreter inside the PostgreSQL server process, security deserves a section of its own, separate from the general “be careful with dynamic SQL” advice I gave earlier.

Trusted vs. untrusted mode matters a lot. The trusted plphp language sandboxes function execution so that ordinary users can’t reach the filesystem or network from inside a function. I never grant USAGE on plphpu (the untrusted variant) to non-superuser roles unless I’ve thought carefully about what that role could do with arbitrary PHP code execution on the database server itself — not just within the database, but potentially against the underlying host.

Function ownership and search_path hijacking. Just like PL/pgSQL functions, PL/PHP functions run with the privileges tied to how they were created and called. If a function is owned by a highly privileged role and doesn’t pin its search_path explicitly, a malicious user with CREATE privileges on a schema earlier in the search path can potentially redefine objects the function relies on, causing it to behave unexpectedly under that privileged context. I always set search_path explicitly for any function running with elevated privileges.

Input from untrusted sources. Any PL/PHP function that builds a query string using PHP concatenation from a parameter that ultimately traces back to user input is a SQL injection risk, exactly as I noted earlier with spi_exec_query(). I treat this the same way I’d treat raw string concatenation in application-layer PHP talking to a database — never acceptable for anything beyond a hardcoded, developer-controlled string.

Performance Tuning Tips

A few specific things I check when a PL/PHP function isn’t performing the way I expect:

• Interpreter startup cost per connection. Since PL/PHP boots a PHP interpreter tied to the backend process, connection churn (frequent new connections rather than reused pooled connections) adds up quickly. I always pair PL/PHP-heavy workloads with a connection pooler like PgBouncer in transaction or session mode, depending on the workload’s needs.
• Avoid calling PL/PHP functions in tight per-row loops over huge tables. If a function is invoked once per row across millions of rows, the cumulative interpreter overhead can dwarf the actual logic being executed. I benchmark against a PL/pgSQL or pure-SQL equivalent before committing to a PL/PHP implementation for anything performance-sensitive at scale.
• Watch memory growth in long-lived sessions. I’ve occasionally seen backend process memory creep upward over the life of a long session that repeatedly calls PL/PHP functions. Restarting connections periodically, or capping pooled connection lifetimes, mitigates this in practice.

Frequently Asked Questions

Can I use Composer packages inside PL/PHP functions? In practice, this is difficult and not well supported, since the embedded PHP interpreter doesn’t share the same autoloading and dependency resolution conventions as a standard PHP application runtime. I generally avoid trying to pull in third-party Composer dependencies inside PL/PHP and instead keep function logic to what’s available in PHP’s standard library.

Is PL/PHP actively maintained? Compared to PL/pgSQL, PL/Python, and PL/Perl, PL/PHP has a smaller maintenance footprint and slower release cadence. I always check compatibility with my specific PostgreSQL version before relying on it for a new project, and I budget extra time for testing during major PostgreSQL upgrades.

Can PL/PHP functions call other PL/PHP functions? Yes — since they’re all ultimately PostgreSQL functions once created, a PL/PHP function can call another PL/PHP function (or a PL/pgSQL, PL/Python, or SQL function) exactly like any other SQL-callable function.

Final Thoughts

PL/PHP is a niche but genuinely useful tool if my team already lives and breathes PHP. It lets me write stored procedures and trigger logic using syntax that feels immediately familiar, without forcing everyone to learn PL/pgSQL from scratch. That said, I go into it with open eyes: it’s a smaller community than PL/pgSQL or PL/Python, installation can be fiddly depending on my OS and PostgreSQL version, and I need to be deliberate about security when working with dynamic SQL inside PHP function bodies.

If I’m maintaining an existing system that already uses PL/PHP, understanding how it works will save me hours of confusion the first time I open a function definition and see PHP syntax where I expected SQL. And if I’m considering it for a new project, I’d weigh it against PL/pgSQL and PL/Python first, and only choose PL/PHP when the team-fit argument is strong enough to outweigh the smaller ecosystem.