Extending PostgreSQL with high level languages (and cats)

By September 30, 2013Technical

In a recent post we extolled the virtues of creating your own brand new operators in PostgreSQL.

SELECT =^_^= FROM happycats;

That’s well and good, but the output was a little lacklustre, returning “meow” for every tuple. We’d like to make it more interesting, and one way to add interesting functionality to Postgres is to embed a procedural language. This lets you juggle data with a little more finesse when it comes to certain operations, compared to the usual relational algebra.

We’re going to use Perl because it’s easy to integrate with Postgres, and is generally a quick and dirty way to Get Stuff Done. When embedded in Postgres it’s referred to as PL/Perl.

Let’s get started. We begin by “installing” the language into the database in which we wish to use the it. This is a peculiarity of Postgres, the language is made available on a per-database basis, rather than server-wide.

# This can also be done within the psql shell, the commandline tool is simply for convenience
postgres@shimako:~$ createlang plperl mykittydb

Next we jump into psql and start defining our functions. This is the same syntax we used to create the “meow” function last time, but now we’re writing in Perl instead of SQL, and we’re actually making use of the input. Our perl code goes right in the body, between the dollar-quotes, and the use of PL/Perl is specified at the end of the definition.

CREATE OR REPLACE FUNCTION nyanify (TEXT) RETURNS TEXT AS $$
    $_ = $_[0];
    s/\S{1,4}/nya/g;
    s/(?<!\s)(?<!^)(?=\s|$)/n/g;
    return $_;
$$ LANGUAGE plperl IMMUTABLE;

This isn’t a terribly readable piece of perl, but it suffices to say that it does a bit of string manipulation with regexes. Perl is a particularly convenient choice of language because the driver handles type conversion, and basic string and integer types will Just Work as expected without fuss.

We’d like to keep our kitty syntax, so at the same time we define a do-nothing function to assign to the other operator:

CREATE OR REPLACE FUNCTION noop (TEXT) RETURNS TEXT AS $$
    SELECT $1::TEXT
$$ LANGUAGE SQL IMMUTABLE;

You’ll notice that this is identical to the “meow” function, but we return the input instead of a fixed string.

Now that we’ve got our functions setup, we bind them to the left-whiskers and right-whiskers operators, just like last time:

DROP OPERATOR IF EXISTS =^ (NONE, TEXT);
CREATE OPERATOR =^ (procedure = nyanify, rightarg = TEXT);

DROP OPERATOR IF EXISTS ^= (TEXT, NONE);
CREATE OPERATOR ^= (procedure = noop, leftarg = TEXT);

We’re almost ready. To spice things up a bit, we’ll add something a bit more literary to our collection of Important Data.

TRUNCATE cats;
INSERT INTO cats (_) VALUES  
    ('You''re wearing sunglasses on a night operation?'),
    ('My vision is augmented'),
    ('Don''t you know what a philanthropist is?'),
    ('Yeah, like Nietzche.'),
    ('No, a philanthropist. Giving to charity.'),
    ('Oh.') ;

And now for the fruits of our labour:

importantdata=# SELECT =^_^= AS nyan FROM cats;                                                    
                          nyan                           
---------------------------------------------------------
 nyanyan nyanyan nyanyanyan nyan nyan nyanyan nyanyanyan
 nyan nyanyan nyan nyanyanyan
 nyanyan nyan nyan nyan nyan nyanyanyanyan nyan
 nyanyan nyan nyanyanyan
 nyan nyan nyanyanyanyan nyanyan nyan nyanyan
 nyan
(6 rows)

This is a very silly example, but it demonstrates just how powerful Postgres’ extensibility can be, with very little effort required on top of actually writing the procedural code. Support for Perl, Python and Tcl is part of the base distribution, and other popular languages can be bolted on as well. If you’re really bent on doing your own thing, you can write your own handler to support other languages.

MySQL ain’t got nothing on this!

Leave a Reply