gistfile1.sql

/*


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. Thanks to @leinweber and 
the rest of Heroku Postgres team for pointing me in the right direction(s).


*/


/*
=================================================================================

## 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
);