PL/Ruby cookbook

Working recipes that use Ruby’s standard library where PostgreSQL alone is awkward. Everything under Tested recipes is exercised verbatim by the regression suite (sql/cookbook.sql), so the code is guaranteed to run on every PostgreSQL version the extension supports.

Ruby’s standard library is available via plain require, including the parts that ship as bundled gems on newer Rubies (csv, bigdecimal, …). Most of these recipes lean on it: json, csv, openssl, zlib, bigdecimal, erb, uri. Gems you install alongside the server’s Ruby are requirable too.

Tested recipes

Validate an email address

URI::MailTo::EMAIL_REGEXP is a maintained pattern that ships with Ruby; no extension packages are needed.

CREATE FUNCTION is_email(text) RETURNS boolean LANGUAGE plruby AS $$
    require 'uri'
    args[0].to_s.match?(URI::MailTo::EMAIL_REGEXP)
$$;

SELECT is_email('user@example.com');   -- t
ALTER TABLE users ADD CONSTRAINT valid_email CHECK (is_email(email));

Redact sensitive keys anywhere in a JSON document

jsonb arrives as a String; JSON.parse turns it into Ruby data, and a recursive lambda rewrites it at any depth. Note the VARIADIC key list.

CREATE FUNCTION jsonb_redact(doc jsonb, VARIADIC keys text[]) RETURNS jsonb
LANGUAGE plruby AS $$
    require 'json'
    redact = lambda { |v|
        case v
        when Hash  then v.to_h { |k, e| [k, keys.include?(k) ? '[redacted]' : redact.call(e)] }
        when Array then v.map { |e| redact.call(e) }
        else v
        end
    }
    JSON.generate(redact.call(JSON.parse(doc)))
$$;

SELECT jsonb_redact(payload, 'password', 'token') FROM api_requests;

Extract every match of a pattern, one row each

String#scan plus return_next makes a set-returning regex extractor.

CREATE FUNCTION extract_emails(text) RETURNS SETOF text LANGUAGE plruby AS $$
    args[0].scan(/[\w+.-]+@[\w.-]+\.\w+/) { |m| return_next m }
    nil
$$;

SELECT * FROM extract_emails('Contact ann@example.com or bob@test.org today.');

HMAC-signed tokens

Sign a value so a client cannot tamper with it, and verify it in constant time on the way back in. Array#pack('m0') handles the Base64 leg with no extra requires (the base64 library left Ruby’s default gems in 3.4).

CREATE FUNCTION sign_token(payload text, key text) RETURNS text LANGUAGE plruby AS $$
    require 'openssl'
    mac = OpenSSL::HMAC.hexdigest('SHA256', args[1], args[0])
    "#{[args[0]].pack('m0')}.#{mac}"
$$;

CREATE FUNCTION verify_token(token text, key text) RETURNS text LANGUAGE plruby AS $$
    require 'openssl'
    data, mac = args[0].split('.', 2)
    payload = data.to_s.unpack1('m0') rescue nil
    if payload
        expected = OpenSSL::HMAC.hexdigest('SHA256', args[1], payload)
        OpenSSL.secure_compare(mac.to_s, expected) ? payload : nil
    end
$$;

verify_token returns the payload when the signature is valid and NULL otherwise.

Password hashing without pgcrypto

PBKDF2-HMAC-SHA256 via OpenSSL::KDF, with the parameters stored alongside the digest. Generate the salt with SecureRandom.hex(16) in practice; it is a parameter here so the recipe is testable.

CREATE FUNCTION hash_password(pw text, salt text) RETURNS text LANGUAGE plruby AS $$
    require 'openssl'
    digest = OpenSSL::KDF.pbkdf2_hmac(args[0], salt: args[1],
                                      iterations: 20_000, length: 32,
                                      hash: 'SHA256')
    "pbkdf2$20000$#{args[1]}$#{digest.unpack1('H*')}"
$$;

CREATE FUNCTION check_password(pw text, stored text) RETURNS boolean LANGUAGE plruby AS $$
    require 'openssl'
    _scheme, iters, salt, hex = args[1].split('$', 4)
    digest = OpenSSL::KDF.pbkdf2_hmac(args[0], salt: salt,
                                      iterations: iters.to_i, length: 32,
                                      hash: 'SHA256')
    OpenSSL.secure_compare(digest.unpack1('H*'), hex.to_s)
$$;

A generic audit trigger

One trigger function for any table: it records the operation and the old/new rows as jsonb. $_TD carries everything it needs.

CREATE TABLE audit_log (
    at_table text,
    op       text,
    old_row  jsonb,
    new_row  jsonb
);

CREATE FUNCTION audit() RETURNS trigger LANGUAGE plruby AS $$
    require 'json'
    old_j = $_TD['old'] ? quote_literal(JSON.generate($_TD['old'])) : 'null'
    new_j = $_TD['new'] ? quote_literal(JSON.generate($_TD['new'])) : 'null'
    spi_exec("insert into audit_log values (" +
             "#{quote_literal($_TD['relname'])}, #{quote_literal($_TD['event'])}, " +
             "#{old_j}, #{new_j})")
    nil
$$;

CREATE TRIGGER accounts_audit AFTER INSERT OR UPDATE OR DELETE ON accounts
    FOR EACH ROW EXECUTE PROCEDURE audit();

Batch processing with periodic commits

A procedure (called with CALL) may commit as it goes, keeping locks short and WAL bounded while it works through a large backlog.

CREATE PROCEDURE process_queue(batch int) LANGUAGE plruby AS $$
    loop do
        r = spi_exec("update cb_queue set done = true where id in " +
                     "(select id from cb_queue where not done limit #{args[0].to_i})")
        spi_commit
        break if spi_processed(r) == 0
    end
$$;

CALL process_queue(1000);

Stream a big scan and stop early

spi_query reads through a cursor a batch at a time (constant memory no matter how large the table), and break abandons the scan as soon as the answer is known. Here: the first gap in an id sequence.

CREATE FUNCTION first_gap(tbl text) RETURNS int LANGUAGE plruby AS $$
    expected = 1
    spi_query("select id from #{quote_ident(args[0])} order by id") do |row|
        break if row['id'] != expected
        expected += 1
    end
    expected
$$;

Parse CSV text into rows

Ruby’s stdlib csv handles quoting, embedded commas and headers correctly; things a hand-rolled split(',') gets wrong.

CREATE FUNCTION csv_rows(text) RETURNS TABLE (name text, qty int) LANGUAGE plruby AS $$
    require 'csv'
    CSV.parse(args[0], headers: true).each do |rec|
        return_next({'name' => rec['name'], 'qty' => rec['qty'].to_i})
    end
    nil
$$;

SELECT * FROM csv_rows(E'name,qty\nwidget,7\nsprocket,12\n');

Compress large text into bytea and back

bytea travels as its hex text form (\x...), so pack/unpack bridges it to raw bytes for Zlib.

CREATE FUNCTION gz(text) RETURNS bytea LANGUAGE plruby AS $$
    require 'zlib'
    "\\x" + Zlib::Deflate.deflate(args[0], Zlib::BEST_COMPRESSION).unpack1('H*')
$$;

CREATE FUNCTION gunz(bytea) RETURNS text LANGUAGE plruby AS $$
    require 'zlib'
    Zlib::Inflate.inflate([args[0].delete_prefix('\x')].pack('H*'))
$$;

Exact decimal arithmetic with BigDecimal

numeric reaches Ruby as a lossless String precisely so it can feed BigDecimal, with no Float rounding anywhere.

CREATE FUNCTION money_share(total numeric, parts int) RETURNS numeric LANGUAGE plruby AS $$
    require 'bigdecimal'
    (BigDecimal(args[0]) / args[1]).round(2, BigDecimal::ROUND_HALF_EVEN).to_s('F')
$$;

SELECT money_share('100.00', 3);   -- 33.33

Slugify a title (Unicode-aware)

NFKD decomposition splits letters from their accents; transcoding to ASCII then drops the accents.

CREATE FUNCTION slugify(text) RETURNS text LANGUAGE plruby AS $$
    args[0].unicode_normalize(:nfkd)
           .encode('ASCII', invalid: :replace, undef: :replace, replace: '')
           .downcase.gsub(/[^a-z0-9]+/, '-').gsub(/\A-|-\z/, '')
$$;

SELECT slugify('Crème Brûlée à la Mode!');   -- creme-brulee-a-la-mode

Render a report with ERB

Ruby’s templating engine, in the database, fed from row data.

CREATE FUNCTION order_summary(cust text, total numeric) RETURNS text LANGUAGE plruby AS $$
    require 'erb'
    template = "Dear <%= cust %>, your order total is $<%= total %>."
    ERB.new(template).result_with_hash(cust: args[0], total: args[1])
$$;

Doc-only recipes (side effects; use with care)

These work, but reach outside the database, so they are not part of the regression suite. Remember that PL/Ruby is untrusted: functions run with the server’s OS-level privileges, and a slow network call happens inside your transaction (do such work from an AFTER trigger, or better, enqueue it and let a worker do the talking).

HTTP webhook from a trigger

CREATE FUNCTION notify_webhook() RETURNS trigger LANGUAGE plruby AS $$
    require 'net/http'
    require 'json'
    uri = URI('https://hooks.example.com/order-created')
    Net::HTTP.post(uri, JSON.generate($_TD['new']),
                   'Content-Type' => 'application/json')
    nil
$$;

Email notification

CREATE FUNCTION mail_alert(subject text, body text) RETURNS void LANGUAGE plruby AS $$
    require 'net/smtp'
    msg = "Subject: #{args[0]}\n\n#{args[1]}"
    Net::SMTP.start('localhost', 25) do |smtp|
        smtp.send_message(msg, 'db@example.com', 'ops@example.com')
    end
$$;

Random tokens and UUIDs

CREATE FUNCTION api_key() RETURNS text LANGUAGE plruby AS $$
    require 'securerandom'
    SecureRandom.urlsafe_base64(32)
$$;

(SecureRandom.uuid likewise generates v4 UUIDs on PostgreSQL versions without gen_random_uuid().)

See doc/plruby.md for the full language reference.