custom-datatypes.md

I wanted an easy way to compare semver based strings without any fancy c-extensions in postgres:

=> select '1.0.1'::semverish >= '1.0.0';
 ?column?
----------
 t

Having no idea how to do this. I was told I can make a custom "domain", this is a datatype that verifies the format of the input value. Then I can use PLV8 to compare two strings in javascript in the database.

Finally I was able to make custom operators that use the PLV8 function so that I can get logical looking semver comparison queries.

*/

/*
=================================================================================
## Create a custom data type "domain" that holds a valid semver string (http://www.postgresql.org/docs/9.3/static/sql-createdomain.html)
*/

CREATE DOMAIN semverish AS VARCHAR(255)
CHECK(
  VALUE ~ '\d+\.\d+\.\d+\.?[\w]*'
);

/*
=================================================================================
## Enable PLV8 (https://blog.heroku.com/archives/2013/6/5/javascript_in_your_postgres)
*/
CREATE EXTENSION IF NOT EXISTS plv8;

/*

=================================================================================
## Create function for evaluating strings against server (https://blog.heroku.com/archives/2013/6/5/javascript_in_your_postgres)
- returns 1 if a > b
- returns -1 if b > a
- returns 0 if a == b
*/

CREATE OR REPLACE FUNCTION compare_semver_strings(a varchar(255), b varchar(255) ) RETURNS integer as $$
    if (a === b) {
       return 0;
    }

    var a_components = a.split(".");
    var b_components = b.split(".");

    var len = Math.min(a_components.length, b_components.length);

    // loop while the components are equal
    for (var i = 0; i < len; i++) {
        // A bigger than B
        if (parseInt(a_components[i]) > parseInt(b_components[i])) {
            return 1;
        }

        // B bigger than A
        if (parseInt(a_components[i]) < parseInt(b_components[i])) {
            return -1;
        }
    }

    // If one's a prefix of the other, the longer one is greater.
    if (a_components.length > b_components.length) {
        return 1;
    }

    if (a_components.length < b_components.length) {
        return -1;
    }

    // Otherwise they are the same.
    return 0;
$$ LANGUAGE plv8 IMMUTABLE STRICT;

/*
=================================================================================
## Create functions for comparison (http://www.postgresql.org/docs/9.3/static/sql-createfunction.html)
- semverish_greater_than
- semverish_less_than
- semverish_equal_to
*/

CREATE OR REPLACE FUNCTION semverish_greater_than(a semverish, b semverish)
RETURNS BOOLEAN AS $$
DECLARE passed BOOLEAN;
BEGIN
        SELECT  (compare_semver_strings(a, b) = 1) INTO passed;

        RETURN passed;
END;
$$  LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION semverish_less_than(a semverish, b semverish)
RETURNS BOOLEAN AS $$
DECLARE passed BOOLEAN;
BEGIN
        SELECT  (compare_semver_strings(a, b) = -1) INTO passed;

        RETURN passed;
END;
$$  LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION semverish_equal_to(a semverish, b semverish)
RETURNS BOOLEAN AS $$
DECLARE passed BOOLEAN;
BEGIN
        SELECT  (compare_semver_strings(a, b) = 0) INTO passed;

        RETURN passed;
END;
$$  LANGUAGE plpgsql;

/*
=================================================================================
## Create operators on semverish type (http://www.postgresql.org/docs/9.3/static/sql-createoperator.html)

- > delegates to semverish_greater_than
- < delegates to semverish_less_than
- = delegates to semverish_equal_to
*/

CREATE OPERATOR > (
    LEFTARG = semverish,
    RIGHTARG = semverish,
    PROCEDURE = semverish_greater_than
);

CREATE OPERATOR < (
    LEFTARG = semverish,
    RIGHTARG = semverish,
    PROCEDURE = semverish_less_than
);

CREATE OPERATOR = (
    LEFTARG = semverish,
    RIGHTARG = semverish,
    PROCEDURE = semverish_equal_to
);