How to Use the PL/Ruby Language in PostgreSQL

Abdul Wahab Junaid

3 years ago

How to Use the PL/Ruby Language in PostgreSQL

PostgreSQL’s procedural language system is one of its most underrated features. Instead of being locked into a single stored procedure language, you can write functions in several different languages depending on what fits your problem best. PL/Ruby is one of these options, letting you write PostgreSQL functions using actual Ruby syntax, with access to Ruby’s expressive string handling, blocks, and standard library.

In this article, I’ll cover what PL/Ruby is, how to get it installed (since it’s not bundled by default), its syntax for writing functions, practical examples, and the trade-offs and troubleshooting considerations you should know about before adopting it.

What Is PL/Ruby?

PL/Ruby is a procedural language extension for PostgreSQL that allows you to write server-side functions in Ruby instead of PL/pgSQL, SQL, or another supported language. It embeds a Ruby interpreter into the PostgreSQL server process, letting your function bodies use real Ruby code — object-oriented constructs, blocks, regular expressions, and access to a chosen subset of Ruby’s standard library.

It’s worth being upfront about something important: PL/Ruby is not part of PostgreSQL core, and unlike PL/pgSQL, PL/Python, PL/Perl, or PL/Tcl, it isn’t maintained as an official contrib module. It’s a community-maintained extension, and its development activity has been considerably less active in recent years compared to more mainstream procedural languages. That doesn’t mean it’s unusable, but it does mean you should go in with realistic expectations about community support, packaging availability on your platform, and compatibility with newer PostgreSQL major versions.

Why You Might Choose PL/Ruby

Your team is already deeply familiar with Ruby and prefers its syntax and idioms over PL/pgSQL’s more SQL-like, verbose style.
You want to reuse small pieces of Ruby logic (regex-heavy string processing, for example) directly inside the database layer.
You’re working in a Ruby-centric shop (Rails applications, for instance) and want database-side logic to feel consistent with the rest of the codebase.

Installing PL/Ruby

Because PL/Ruby isn’t bundled with PostgreSQL, you’ll need to install it separately. On Debian/Ubuntu-based systems, package availability varies significantly by distribution version, so check your package manager first:

sudo apt search postgresql-plruby

If a prebuilt package isn’t available for your PostgreSQL version, you may need to build it from source against your installed Ruby development headers and your PostgreSQL server development package (postgresql-server-dev-XX). Given its lower maintenance activity, building from source is a real possibility you should be prepared for, and it’s worth checking the project’s repository directly for build instructions compatible with your PostgreSQL version before committing to this language for a production system.

Once the shared library is installed on the server, enabling it in a specific database follows the standard extension pattern:

CREATE EXTENSION plruby;

If you need untrusted-mode capabilities (explained below), you’d instead install the untrusted variant, typically:

CREATE EXTENSION plrubyu;

Trusted vs. Untrusted Mode

Like other PostgreSQL procedural languages, PL/Ruby comes in two flavors:

plruby (trusted) — restricts what the function can do, sandboxing away direct filesystem access, network access, and other operations that could be dangerous if run by an unprivileged user. Any authenticated user with the right function-creation privileges can use the trusted language.
plrubyu (untrusted) — has full access to the underlying system, including files and network resources. Because of that power, only superusers can create functions in the untrusted variant.

For almost all application logic, you’ll want the trusted plruby. Reach for plrubyu only when you genuinely need system-level access from within a function, and even then, treat it with the same caution you’d apply to any code running with elevated privileges directly inside your database server process.

Basic Function Syntax

A PL/Ruby function is defined much like any other PostgreSQL function, with the language set to plruby and the body written in Ruby:

CREATE OR REPLACE FUNCTION greet(name TEXT)
RETURNS TEXT
AS $$
  "Hello, #{name}!"
$$ LANGUAGE plruby;

A few things to note about how PL/Ruby functions work:

The last evaluated expression in the function body is automatically returned, following normal Ruby semantics — there’s no explicit return keyword required, though you can use one if you prefer.
Function arguments are made available as local variables matching their SQL parameter names.
Ruby exceptions raised inside the function body propagate back to PostgreSQL as errors, aborting the current transaction (or subtransaction, if inside an exception block in the calling context).

Practical Examples

Example 1: Basic String Manipulation

CREATE OR REPLACE FUNCTION reverse_words(sentence TEXT)
RETURNS TEXT
AS $$
  sentence.split(' ').reverse.join(' ')
$$ LANGUAGE plruby;

SELECT reverse_words('the quick brown fox');
-- returns: fox brown quick the

Example 2: Using Regular Expressions

Ruby’s regex handling is one of its strong points, and it’s directly usable inside PL/Ruby functions:

CREATE OR REPLACE FUNCTION extract_domain(email TEXT)
RETURNS TEXT
AS $$
  match = email.match(/@([\w\.\-]+)/)
  match ? match[1] : nil
$$ LANGUAGE plruby;

SELECT extract_domain('user@example.com');
-- returns: example.com

Example 3: Returning a Set of Rows

PL/Ruby functions can return multiple rows by yielding results, similar to a generator pattern:

CREATE OR REPLACE FUNCTION split_to_rows(input TEXT, delimiter TEXT)
RETURNS SETOF TEXT
AS $$
  input.split(delimiter).each { |word| yield word }
$$ LANGUAGE plruby;

SELECT * FROM split_to_rows('apple,banana,cherry', ',');

This returns three rows, one per fruit.

Example 4: Working With PostgreSQL Data Inside a Function

PL/Ruby exposes a plruby_spi_exec style interface (details vary by PL/Ruby version) that lets you run SQL queries from within the Ruby function body, similar to how PL/Python and PL/Perl expose SPI (Server Programming Interface) access:

CREATE OR REPLACE FUNCTION count_orders_for_customer(cust_id INT)
RETURNS INT
AS $$
  result = plan("SELECT count(*) AS cnt FROM orders WHERE customer_id = $1", ["int4"]).exec([cust_id])
  result[0]["cnt"].to_i
$$ LANGUAGE plruby;

Exact API details for querying can differ between PL/Ruby versions and forks, so always check the specific documentation for the version you’ve installed rather than assuming full parity with what’s shown here.

Example 5: Returning Composite Types

CREATE TYPE name_parts AS (first_name TEXT, last_name TEXT);

CREATE OR REPLACE FUNCTION split_full_name(full_name TEXT)
RETURNS name_parts
AS $$
  parts = full_name.split(' ', 2)
  { "first_name" => parts[0], "last_name" => parts[1] || "" }
$$ LANGUAGE plruby;

SELECT * FROM split_full_name('Jane Doe');

Common Use Cases

Text and string processing logic where Ruby’s regex and string manipulation features feel more natural than PL/pgSQL’s more limited built-in string functions.
Small, self-contained transformation functions used inside triggers or computed columns, where the logic is genuinely simpler to express in Ruby.
Teams standardizing on Ruby across their stack, wanting database-side logic to be readable by the same engineers maintaining the application layer.
Prototyping logic quickly using Ruby’s expressive, flexible syntax before deciding whether it needs to move to a more performance-critical language.

Troubleshooting Common Issues

“could not load library” or Extension Fails to Create

This almost always points to either a missing shared library file on the server, or a version mismatch between the compiled PL/Ruby extension and your installed PostgreSQL or Ruby version. Since prebuilt packages aren’t reliably available for every combination, double-check you’re building against matching header versions if you compiled it yourself.

Function Creation Fails With “language plruby does not exist”

This means the extension hasn’t been created in the current database yet. Run:

CREATE EXTENSION plruby;

If that also fails, it means the shared library isn’t installed at the OS level, and you’ll need to address that first (see above).

Untrusted Operations Failing Under Trusted Mode

If your function tries to open a file, make a network call, or perform another restricted operation and you’re using plruby (trusted), it will be blocked by the sandboxing. This is by design. If you genuinely need that capability, you’d need plrubyu, and the function would then require superuser privileges to create.

Performance Concerns With Frequent Calls

Procedural language functions in general carry more overhead than native SQL or well-indexed queries, and PL/Ruby is no exception — every call involves crossing into the embedded Ruby interpreter. For hot-path functions called extremely frequently (millions of times per query, for example), consider whether the logic could be expressed as plain SQL or PL/pgSQL instead, reserving PL/Ruby for cases where its expressiveness genuinely pays for itself.

Compatibility Issues After a PostgreSQL Major Version Upgrade

Given PL/Ruby’s slower release cadence relative to PostgreSQL’s own release cycle, it’s worth explicitly testing PL/Ruby function behavior in a staging environment before upgrading a production PostgreSQL major version, rather than assuming compatibility will carry over automatically.

Best Practices

Default to trusted mode (plruby) unless you have an explicit, well-understood reason to need untrusted system access.
Keep PL/Ruby functions small and focused. Use it for genuinely Ruby-appropriate logic — string processing, regex-heavy transformations — rather than large, complex business logic that would be clearer as a well-structured PL/pgSQL function or handled in the application layer entirely.
Test thoroughly across PostgreSQL version upgrades, given the language’s less frequent maintenance cadence compared to officially bundled procedural languages.
Avoid PL/Ruby for extremely hot-path, high-frequency functions where the interpreter call overhead could become a genuine bottleneck — benchmark before committing to this approach at scale.
Document why PL/Ruby was chosen for a given function, since it’s a less common choice than PL/pgSQL, and future maintainers may not be familiar with it at all.
Have a fallback plan. Because this is a community-maintained, less actively developed extension, consider whether a small amount of business-critical logic being locked into PL/Ruby creates a maintenance risk worth mitigating — for instance, by keeping the equivalent logic documented or mirrored in another language.

Comparing PL/Ruby to Other Procedural Language Options

It helps to see PL/Ruby in context against the other procedural languages PostgreSQL supports, since the choice usually comes down to team familiarity and specific workload characteristics rather than one language being objectively “best.”

PL/pgSQL is the default, bundled-with-core choice, tightly integrated with SQL and generally the right starting point for most stored procedures, especially ones that are mostly data manipulation with light procedural logic wrapped around them.

PL/Python is far more actively maintained and widely used than PL/Ruby, with a large ecosystem and broad familiarity among developers, making it a common choice when a team wants a general-purpose scripting language inside the database but isn’t specifically committed to Ruby.

PL/Perl is another long-standing, officially maintained option, historically popular for text-processing-heavy functions before Python’s rise in general popularity.

PL/Ruby, by comparison, occupies a narrower niche: teams that are specifically and deeply invested in Ruby, often in a Rails-centric organization, where consistency with the application layer’s language carries real value for maintainability and shared team expertise.

If your team doesn’t have a strong existing Ruby investment, it’s worth seriously considering PL/Python or sticking with well-written PL/pgSQL before reaching for PL/Ruby specifically, given the comparative maturity and support differences.

Error Handling in PL/Ruby Functions

Because Ruby exceptions propagate up as PostgreSQL errors, you can use standard Ruby exception handling within a function to catch and handle specific error conditions gracefully rather than letting the whole transaction abort unexpectedly:

CREATE OR REPLACE FUNCTION safe_divide(a NUMERIC, b NUMERIC)
RETURNS NUMERIC
AS $$
  begin
    a / b
  rescue ZeroDivisionError
    nil
  end
$$ LANGUAGE plruby;

SELECT safe_divide(10, 0);
-- returns: NULL, instead of raising an error

This pattern is useful for functions where a graceful fallback value is more appropriate than aborting the calling transaction entirely, though for many database-level integrity concerns, letting the error propagate and handling it at the SQL or application level is often the more correct and transparent approach.

Testing PL/Ruby Functions Thoroughly

Since PL/Ruby function bodies are just text passed to the extension at creation time, there’s no built-in syntax checking at definition time beyond very basic parsing — a typo or logic error inside the Ruby code often won’t surface until the function is actually called. This makes thorough testing more important than it might be with a more heavily tooled language environment.

A reasonable practice is to prototype the core logic as a standalone Ruby script outside the database first, verify its behavior with normal Ruby testing tools, and only then wrap the verified logic into the SQL function definition — rather than iterating directly against CREATE OR REPLACE FUNCTION statements inside psql, which makes for a slower and more error-prone feedback loop.

Using PL/Ruby in Trigger Functions

Like other procedural languages, PL/Ruby functions can serve as trigger functions, giving you Ruby-flavored logic for row-level validation or transformation on insert, update, or delete:

CREATE OR REPLACE FUNCTION normalize_email_trigger()
RETURNS TRIGGER
AS $$
  if NEW['email']
    NEW['email'] = NEW['email'].downcase.strip
  end
  NEW
$$ LANGUAGE plruby;

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

Inside a trigger function, NEW and OLD are exposed as hash-like structures representing the row being inserted/updated and the previous row’s values (for updates and deletes), respectively. Modifying NEW and returning it changes what actually gets written to the table, which is the standard trigger pattern shared across all of PostgreSQL’s procedural languages, just expressed with Ruby’s hash syntax instead of PL/pgSQL’s record syntax.

Weighing PL/Ruby for Production Use

Before adopting PL/Ruby for anything business-critical, it’s worth having an honest conversation on the team about the trade-offs involved. The core appeal — writing database functions in familiar, expressive Ruby — is real, but it comes bundled with meaningfully more operational risk than reaching for PL/pgSQL or even PL/Python, given the smaller maintainer community and less frequent compatibility testing against new PostgreSQL releases.

A reasonable middle ground some teams land on: use PL/Ruby for internal tooling, data migration scripts, or non-critical convenience functions where its expressiveness genuinely speeds up development, while keeping core business logic — data integrity constraints, critical calculations, anything the application fundamentally depends on — in PL/pgSQL or at the application layer, where language support and long-term maintainability are on firmer footing. This way, if PL/Ruby support for your PostgreSQL version ever becomes a genuine blocker during a future upgrade, the blast radius of needing to rewrite that logic in another language stays contained to lower-stakes functions rather than core application behavior.

Keeping PL/Ruby Functions Maintainable Long-Term

Because PL/Ruby is a smaller, less common choice, it’s worth investing extra effort in keeping its usage discoverable and well-documented within a codebase. Adding a clear comment block above each function explaining what it does and why Ruby specifically was chosen over PL/pgSQL helps future maintainers — including a future version of yourself — avoid wasted time puzzling over unfamiliar syntax embedded inside a SQL migration file. Grouping all PL/Ruby function definitions into a clearly named migration file or schema section, rather than scattering them throughout a larger codebase, also makes it easier to audit exactly how much of your application depends on this less mainstream extension at any given point in time.

Wrapping Up

PL/Ruby is a genuinely interesting option if your team lives and breathes Ruby and wants that same expressiveness inside the database layer. It shines for string-heavy, regex-driven transformation logic where Ruby’s syntax is simply more pleasant to write and read than the alternatives. That said, its status as a community-maintained, less actively developed extension means it’s worth going in with clear eyes about installation friction, long-term maintenance, and version compatibility — especially for anything business-critical. For most teams, it’s best reserved for well-scoped, non-performance-critical functions where its expressiveness genuinely earns its place.