PL/php cookbook

Practical, self-contained recipes showing where PL/php earns its keep over plpgsql: PHP’s standard library. Everything in the first section is run by the cookbook regression test on every supported PostgreSQL version: if it’s on this page, it works.

The embedded interpreter ships with json, pcre, openssl, sodium, hash (including password_hash), filter, iconv, zlib, DOM/SimpleXML, sockets, and fileinfo. Remember PL/php is untrusted and superuser-only: these functions run with the full privileges of the server’s OS user (see Security).

Tested recipes

Validate data with filter_var

No hand-maintained email regex. IMMUTABLE STRICT makes it usable in CHECK constraints and indexes:

CREATE FUNCTION is_valid_email(text) RETURNS boolean
LANGUAGE plphp IMMUTABLE STRICT AS $$
    return filter_var($args[0], FILTER_VALIDATE_EMAIL) !== false;
$$;

CREATE TABLE subscribers (
    email text PRIMARY KEY CHECK (is_valid_email(email))
);

FILTER_VALIDATE_URL, FILTER_VALIDATE_IP (with FILTER_FLAG_IPV6, FILTER_FLAG_NO_PRIV_RANGE, …) and FILTER_VALIDATE_DOMAIN work the same way.

bcrypt passwords without pgcrypto

CREATE FUNCTION hash_password(text) RETURNS text
LANGUAGE plphp STRICT AS $$
    return password_hash($args[0], PASSWORD_BCRYPT);
$$;

CREATE FUNCTION check_password(plain text, hashed text) RETURNS boolean
LANGUAGE plphp STRICT AS $$
    return password_verify($args[0], $args[1]);
$$;

SELECT check_password('s3cret', hash_password('s3cret'));   -- true

password_hash salts automatically; store its output as-is. PASSWORD_ARGON2I is available too if libargon2 was compiled in.

HMAC-signed tokens

Issue and verify tamper-proof tokens with a server-side secret, no extension needed:

CREATE FUNCTION sign_token(payload text, secret text) RETURNS text
LANGUAGE plphp STRICT AS $$
    return $args[0] . "." . hash_hmac("sha256", $args[0], $args[1]);
$$;

CREATE FUNCTION verify_token(token text, secret text) RETURNS text
LANGUAGE plphp STRICT AS $$
    $pos = strrpos($args[0], ".");
    if ($pos === false)
        return null;
    $payload = substr($args[0], 0, $pos);
    $mac     = substr($args[0], $pos + 1);
    return hash_equals(hash_hmac("sha256", $payload, $args[1]), $mac)
        ? $payload : null;
$$;

verify_token returns the payload when the signature checks out and NULL otherwise, and uses constant-time hash_equals.

Reshape JSON recursively

Deeply redact keys anywhere in a document. Awkward with jsonb operators, three lines of PHP:

CREATE FUNCTION jsonb_redact(doc jsonb, keys text[]) RETURNS jsonb
LANGUAGE plphp STRICT AS $$
    $doc  = json_decode($args[0], true);
    $keys = $args[1];
    $walk = function (&$node) use (&$walk, $keys) {
        foreach ($node as $k => &$v) {
            if (in_array((string) $k, $keys, true))
                $v = "[redacted]";
            elseif (is_array($v))
                $walk($v);
        }
    };
    $walk($doc);
    return json_encode($doc);
$$;

SELECT jsonb_redact(
    '{"user":"jd","password":"x","profile":{"api_key":"y","city":"Olympia"}}',
    ARRAY['password', 'api_key']);
-- {"user": "jd", "profile": {"city": "Olympia", "api_key": "[redacted]"}, "password": "[redacted]"}

Extract with a regex, return a set

preg_match_all + return_next turns any regex into a set-returning function:

CREATE FUNCTION extract_urls(text) RETURNS SETOF text
LANGUAGE plphp IMMUTABLE STRICT AS $$
    preg_match_all('~https?://[^\s<>")\']+~', $args[0], $m);
    foreach ($m[0] as $url)
        return_next($url);
    return;
$$;

SELECT extract_urls('see https://example.com/a and http://foo.bar/b?x=1.');

A generic audit trigger

One trigger function for any table: diffs OLD against NEW into jsonb, and caches its INSERT plan in $_SHARED so it is prepared once per session:

CREATE TABLE audit_log (tab text, op text, changed jsonb);

CREATE FUNCTION audit() RETURNS trigger LANGUAGE plphp AS $$
    global $_SHARED;

    $old = $_TD['event'] == 'INSERT' ? array() : $_TD['old'];
    $new = $_TD['event'] == 'DELETE' ? array() : $_TD['new'];

    $diff = array();
    foreach ($new as $k => $v)
        if (!array_key_exists($k, $old) || $old[$k] !== $v)
            $diff[$k] = array("from" => $old[$k] ?? null, "to" => $v);
    foreach ($old as $k => $v)
        if (!array_key_exists($k, $new))
            $diff[$k] = array("from" => $v, "to" => null);

    if (!isset($_SHARED['audit_plan']))
        $_SHARED['audit_plan'] = spi_prepare(
            'insert into audit_log values ($1, $2, $3)',
            'text', 'text', 'jsonb');
    spi_exec_prepared($_SHARED['audit_plan'],
                      $_TD['relname'], $_TD['event'],
                      json_encode($diff ?: new stdClass()));
    return;
$$;

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

Batch processing with periodic commits

In a procedure CALLed non-atomically, commit every batch so long jobs don’t hold one giant transaction. Note the loop re-queries rather than holding a cursor: portals do not survive spi_commit (a spi_fetchrow after commit just returns false).

CREATE PROCEDURE process_queue(batch_size int) LANGUAGE plphp AS $$
    while (true) {
        $r = spi_exec("update queue set done = true
                       where id in (select id from queue
                                    where not done
                                    order by id limit {$args[0]})");
        if (spi_processed($r) == 0)
            break;
        pg_raise('notice', 'processed ' . spi_processed($r) . ' rows');
        spi_commit();
    }
$$;

CALL process_queue(1000);

Stream a big scan, stop early

A cursor (spi_query) reads rows one at a time in constant memory. Close it as soon as you have your answer instead of materializing millions of rows:

CREATE FUNCTION first_exceeding(threshold numeric) RETURNS int
LANGUAGE plphp STRICT AS $$
    $c = spi_query("select id, amount from ledger order by id");
    $total = 0;
    while ($row = spi_fetchrow($c)) {
        $total += $row['amount'];
        if ($total > $args[0]) {
            spi_cursor_close($c);
            return $row['id'];
        }
    }
    return null;
$$;

Read a CSV file into rows

Server-side file ETL without COPY’s format restrictions: fgetcsv handles quoting/escaping, and RETURNS TABLE + return_next(array) does the rest:

CREATE FUNCTION load_csv(path text)
RETURNS TABLE (a text, b text, c text)
LANGUAGE plphp STRICT AS $$
    $fh = fopen($args[0], "r");
    if ($fh === false)
        pg_raise('error', "cannot open {$args[0]}");
    while (($rec = fgetcsv($fh, null, ",", "\"", "\\")) !== false)
        return_next($rec);
    fclose($fh);
    return;
$$;

SELECT * FROM load_csv('/srv/import/products.csv');

Shred XML into rows

CREATE FUNCTION xml_items(doc xml)
RETURNS TABLE (name text, qty int)
LANGUAGE plphp STRICT AS $$
    $doc = simplexml_load_string($args[0]);
    foreach ($doc->item as $item)
        return_next(array((string) $item['name'], (int) $item['qty']));
    return;
$$;

SELECT * FROM xml_items('<order><item name="bolt" qty="9"/><item name="nut" qty="4"/></order>');

Compress large text into bytea

CREATE FUNCTION gz(text) RETURNS bytea
LANGUAGE plphp IMMUTABLE STRICT AS $$
    return "\\x" . bin2hex(gzcompress($args[0], 9));
$$;

CREATE FUNCTION gunz(bytea) RETURNS text
LANGUAGE plphp IMMUTABLE STRICT AS $$
    return gzuncompress(hex2bin(substr($args[0], 2)));
$$;

SELECT gunz(gz(long_text)) = long_text FROM documents;   -- true

(PostgreSQL TOAST already compresses transparently; this is for when you want the compressed bytes explicitly, to hand to a client or use with SET STORAGE EXTERNAL.)

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

These work but are deliberately not in the regression test: they touch the outside world, and they run inside your transaction on the backend. A slow webhook blocks the INSERT that fired it, and a rolled-back transaction does not un-send an HTTP request or an email. For anything latency-sensitive, write to a queue table instead and let a worker drain it.

HTTP webhook from a trigger

No curl needed: PHP’s HTTP stream wrapper does POSTs, and a short timeout is mandatory:

CREATE FUNCTION notify_webhook() RETURNS trigger LANGUAGE plphp AS $$
    $ctx = stream_context_create(array("http" => array(
        "method"  => "POST",
        "header"  => "Content-Type: application/json",
        "content" => json_encode($_TD['new']),
        "timeout" => 2,
    )));
    @file_get_contents("https://hooks.example.com/row-changed", false, $ctx);
    return;   -- never fail the DML because the webhook is down
$$;

Email notification

CREATE FUNCTION mail_alert(subject text, body text) RETURNS void
LANGUAGE plphp STRICT AS $$
    mail("ops@example.com", $args[0], $args[1]);
$$;

Requires a working sendmail on the database server.

Slugify with transliteration

iconv’s //TRANSLIT is locale-dependent, so results can vary between systems: good for display, not for keys that must be stable everywhere:

CREATE FUNCTION slugify(text) RETURNS text LANGUAGE plphp STRICT AS $$
    $s = iconv("UTF-8", "ASCII//TRANSLIT//IGNORE", $args[0]);
    $s = strtolower(preg_replace("/[^A-Za-z0-9]+/", "-", $s));
    return trim($s, "-");
$$;