Extending PostgreSQL for fun: with cats

By September 18, 2013Technical

Perhaps you’ve thought I wish I had more cats in my Postgres database before. We certainly have.

Just the other day we were lamenting some of the differences between MySQL and PostgreSQL, particularly the way that MySQL has case-insensitive matching using the LIKE operator, while Postgres has LIKE and ILIKE. This got us thinking, it’d be amusing to have more vague (and hilariously unwieldy) operators, such as:

SELECT * FROM foo WHERE a VAGUELY RESEMBLES b;

Something like this isn’t too implausible. It’s similar to what a full-text search entails, but it’s far from trivial. This got us thinking: if we can’t easily do that, can we at least have some amusing query syntax? Yes we can!

It turns out that a bare underscore is a valid field name in a table, so we created a dummy table and populated it with some Valuable Data:

CREATE TABLE cats (_ TEXT);
INSERT INTO cats (_) VALUES ('Tax return 2013'), ('Nuclear launch codes'), ('NSA leaked documents');

Postgres’ rich extensibility options lets us define arbitrary functions, and also new symbol-y operators, meaning you can replicate PHP’s daft === operator (“really-really-really-equal, scout’s honour”) if you want to. We’re lucky that the characters we wanted to use were on the whitelist, so a few commands later…

CREATE OR REPLACE FUNCTION meow (TEXT) RETURNS TEXT AS $$SELECT 'meow'::TEXT$$ LANGUAGE SQL IMMUTABLE;
CREATE OPERATOR =^ ( procedure = meow, rightarg = text);
CREATE OPERATOR ^= ( procedure = meow, leftarg = text);

Huzzah! We’ve got us a feline simulator and a couple of brand new unary operators, let’s test it out:

SELECT =^_^= AS nyan FROM cats;

 nyan 
------
 meow
 meow
 meow
(3 rows)

It’s everything we’ve ever dreamt of!!!

2 Comments

Leave a Reply