Pure PostgreSQL semantic version

example.sql

CREATE TABLE versions
(
    version semver not null unique
);
INSERT INTO versions (version)
VALUES (ROW (1, 0, 0, 'alpha', '')),
       (ROW (1, 0, 0, 'alpha.1', '')),
       (ROW (1, 0, 0, 'alpha.beta', '')),
       (ROW (1, 0, 0, 'beta', '')),
       (ROW (1, 0, 0, 'beta.2', '')),
       (ROW (1, 0, 0, 'beta.11', '')),
       (ROW (1, 0, 0, 'rc.1', '')),
       (ROW (1, 0, 0, '', '')),
       (ROW (1, 0, 1, '', '')),
       (ROW (1, 1, 0, '', '')),
       (ROW (1, 1, 1, '', '')),
       (ROW (2, 0, 0, '', ''));

output.txt

SELECT semver_format(version)
FROM versions
ORDER BY version;

+----------------+
|semver_format   |
+----------------+
|2.0.0           |
|1.1.1           |
|1.1.0           |
|1.0.1           |
|1.0.0           |
|1.0.0-rc.1      |
|1.0.0-beta.11   |
|1.0.0-beta.2    |
|1.0.0-beta      |
|1.0.0-alpha.beta|
|1.0.0-alpha.1   |
|1.0.0-alpha     |
+----------------+


EXPLAIN ANALYSE SELECT semver_format(version)
FROM versions
ORDER BY version;

Index Only Scan Backward using versions_version_key on versions  (cost=0.14..15.31 rows=12 width=64) (actual time=0.034..0.070 rows=12 loops=1)
  Heap Fetches: 12
Planning Time: 0.045 ms
Execution Time: 0.085 ms

semver.sql

CREATE TYPE semver AS
(
    major      integer,
    minor      integer,
    patch      integer,
    prerelease text,
    build      text

);

semver_cmp.sql

CREATE OR REPLACE FUNCTION semver_prerelease_cmp(text, text)
    RETURNS integer AS
$$
DECLARE
    tmp record;
BEGIN
    IF $1 = $2 THEN
        RETURN 0;
    ELSIF ($1 <> '') IS NOT TRUE THEN
        RETURN 1;
    ELSIF ($2 <> '') IS NOT TRUE THEN
        RETURN -1;
    END IF;

    FOR tmp IN SELECT t.lhs, t.rhs, t.lhs ~ '^\d+$' AS lhs_isnum, t.rhs ~ '^\d+$' AS rhs_isnum
               FROM unnest(string_to_array($1, '.'), string_to_array($2, '.')) AS t(lhs, rhs)
        LOOP
            CONTINUE WHEN tmp.lhs = tmp.rhs;

            IF tmp.lhs IS NULL THEN
                RETURN -1;
            ELSIF tmp.rhs IS NULL THEN
                RETURN 1;
            ELSIF tmp.lhs_isnum AND tmp.rhs_isnum THEN
                IF tmp.lhs::int < tmp.rhs::int THEN
                    RETURN -1;
                ELSE
                    RETURN 1;
                END IF;
            ELSIF tmp.lhs_isnum THEN
                RETURN -1;
            ELSIF tmp.rhs_isnum THEN
                RETURN 1;
            ELSIF tmp.lhs < tmp.rhs THEN
                RETURN -1;
            ELSE
                RETURN 1;
            END IF;
        END LOOP;
    RETURN 0;
END;
$$ LANGUAGE plpgsql IMMUTABLE;


CREATE OR REPLACE FUNCTION semver_cmp(semver, semver) RETURNS integer AS
$$
BEGIN
    IF $1.major < $2.major THEN
        RETURN -1;
    ELSIF $1.major > $2.major THEN
        RETURN 1;
    END IF;

    IF $1.minor < $2.minor THEN
        RETURN -1;
    ELSIF $1.minor > $2.minor THEN
        RETURN 1;
    END IF;

    IF $1.patch < $2.patch THEN
        RETURN -1;
    ELSIF $1.patch > $2.patch THEN
        RETURN 1;
    END IF;

    RETURN semver_prerelease_cmp($1.prerelease, $2.prerelease);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

semver_operator_class.sql

CREATE OPERATOR CLASS semver_btree_ops DEFAULT FOR TYPE semver USING btree AS
    OPERATOR 1 <,
    OPERATOR 2 <=,
    OPERATOR 3 =,
    OPERATOR 4 >=,
    OPERATOR 5 >,

    FUNCTION 1 semver_cmp;

semver_operators.sql

CREATE FUNCTION semver_lt(semver, semver) RETURNS boolean AS $$
BEGIN
    RETURN semver_cmp($1, $2) < 0;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE FUNCTION semver_le(semver, semver) RETURNS boolean AS $$
BEGIN
    RETURN semver_cmp($1, $2) <= 0;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE FUNCTION semver_gt(semver, semver) RETURNS boolean AS $$
BEGIN
    RETURN semver_cmp($1, $2) > 0;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE FUNCTION semver_ge(semver, semver) RETURNS boolean AS $$
BEGIN
    RETURN semver_cmp($1, $2) >= 0;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE FUNCTION semver_eq(semver, semver) RETURNS boolean AS $$
BEGIN
    RETURN semver_cmp($1, $2) = 0;
END;
$$ LANGUAGE plpgsql IMMUTABLE;


CREATE OPERATOR < (PROCEDURE = semver_lt, LEFTARG = semver, RIGHTARG = semver);
CREATE OPERATOR <= (PROCEDURE = semver_le, LEFTARG = semver, RIGHTARG = semver);
CREATE OPERATOR > (PROCEDURE = semver_gt, LEFTARG = semver, RIGHTARG = semver);
CREATE OPERATOR >= (PROCEDURE = semver_ge, LEFTARG = semver, RIGHTARG = semver);
CREATE OPERATOR = (PROCEDURE = semver_eq, LEFTARG = semver, RIGHTARG = semver);