-- Complain if script is sourced in `psql`, rather than via `CREATE EXTENSION`. \echo Use "CREATE EXTENSION pg_text_semver" to load this file. \quit -------------------------------------------------------------------------------------------------------------- comment on extension pg_text_semver is $markdown$ # PostgreSQL semantic versioning extension: `pg_text_semver` [![PGXN version](https://badge.fury.io/pg/pg_text_semver.svg)](https://badge.fury.io/pg/pg_text_semver) This is [not the only](#alternatives-to-pg_text_semver) Postgres extension that implements the [Semantic Versioning 2.0.0 Specification](https://semver.org/spec/v2.0.0.html); what sets this one apart is that offers a simple `semver` `DOMAIN` based on Postgres' built-in `text` type. ## `pg_text_semver` features * Values of the [`semver`](#domain-semver) domain type can be compared: - using the usual comparison operators—`<`, '>`, `<=`, `>=`, `<>`, `!=`, and `=`—; or - using the [`semver_cmp(semver, semver)`](#function-semver_cmp-semver-semver) function directly. * The [`semver`](#domain-semver) domain is constrained using the [`semver_regexp()`](#function-semver_regexp-boolean) function. This means that you can also access this regular expression directly, either with capturing groups or without, by passing `true` or `false` to the function, respectively. * Parsed `semver` values can be obtained by casting to the `semver_parsed` composite type. * All the same comparison operators and functions exist for `semver_parsed` as do for plain `semver` values. * In addition, `semver_parsed` values can be used for sorting and indexing. * `semver_parsed` values can be serialized to a properly formatted semantic version by casting (back) to `semver` or `text`. * There's a separate [`semver_prerelease`](#domain-semver_prerelease) domain type, so that the prerelease portions of semantic versions can be validated or compared (using the [`semver_prerelease_cmp()`](#function-semver_prerelease_cmp-semver_prerelease-semver_prerelease) function. ## Installation Basically: ``` sudo make install ``` Like most Postgres extensions, `pg_text_semver` requires [Postgres' PGXS build infrastructure](https://www.postgresql.org/docs/current/extend-pgxs.html) for `make install` to work. Depending on your OS, this may or may not require you to install anything in addition to the package you need to install to get the `postgres` daemon up and running. ## `pg_text_semver` usage See the [`test__pg_text_semver()`](#procedure-test__pg_text_semver) procedure for examples of how to use the types, operators and functions provides by this extension. ## Alternatives to `pg_text_semver` An excellent pre-existing alternative to `pg_text_semver` is the [`semver`](https://github.com/theory/pg-semver) extension. `semver` is written in C and therefore more efficient than `pg_text_semver`. But, as of March 2, 2023, [it treats the parts of the semantic version as 32-bit integers](https://github.com/theory/pg-semver/issues/47), while the spec prescribes no limitations to the size of each number. Besides [`semver`](https://github.com/theory/pg-semver) (repo name `pg-semver`, with a dash), there is also the older [PG SEMVER](https://github.com/eendroroy/pg_semver) (repo name `pg_semver`, with an underscore). The `pg_text_semver` author only discovered PG SEMVER _after_ releasing his own extension, and hasn't tried it; something left to do… ## Authors and contributors * [Rowan](https://www.bigsmoke.us/) originated this extension in 2022 while developing the PostgreSQL backend for the [FlashMQ SaaS MQTT cloud broker](https://www.flashmq.com/). Rowan does not like to see himself as a tech person or a tech writer, but, much to his chagrin, [he _is_](https://blog.bigsmoke.us/category/technology). Some of his chagrin about his disdain for the IT industry he poured into a book: [_Why Programming Still Sucks_](https://www.whyprogrammingstillsucks.com/). Much more than a “tech bro”, he identifies as a garden gnome, fairy and ork rolled into one, and his passion is really to [regreen and reenchant his environment](https://sapienshabitat.com/). One of his proudest achievements is to be the third generation ecological gardener to grow the wild garden around his beautiful [family holiday home in the forest of Norg, Drenthe, the Netherlands](https://www.schuilplaats-norg.nl/) (available for rent!). $markdown$; -------------------------------------------------------------------------------------------------------------- create or replace function pg_text_semver_readme() returns text volatile set search_path from current set pg_readme.include_view_definitions to 'true' set pg_readme.include_routine_definitions_like to '{test__%}' language plpgsql as $plpgsql$ declare _readme text; begin create extension if not exists pg_readme cascade; _readme := pg_extension_readme('pg_text_semver'::name); raise transaction_rollback; -- to `DROP EXTENSION` if we happened to `CREATE EXTENSION` for just this. exception when transaction_rollback then return _readme; end; $plpgsql$; comment on function pg_text_semver_readme() is $md$This function utilizes the `pg_readme` extension to generate a thorough README for this extension, based on the `pg_catalog` and the `COMMENT` objects found therein. $md$; -------------------------------------------------------------------------------------------------------------- create or replace function pg_text_semver_meta_pgxn() returns jsonb stable language sql return jsonb_build_object( 'name' ,'pg_text_semver' ,'abstract' ,'PostgreSQL semantic versioning extension, with comparison functions and operators.' ,'description' ,'The pg_text_semver extension offers a "semver" DOMAIN type based on Postgres'' built-in "text" type.' ,'version' ,(select extversion from pg_extension where extname = 'pg_text_semver') ,'maintainer' ,array[ 'Rowan Rodrik van der Molen ' ] ,'license' ,'postgresql' ,'prereqs' ,'{ "develop": { "recommends": { "pg_readme": 0 } } }'::jsonb ,'provides' ,('{ "pg_safer_settings": { "file": "pg_text_semver--1.1.0.sql", "version": "' || (select extversion from pg_extension where extname = 'pg_text_semver') || '", "docfile": "README.md" } }')::jsonb ,'resources' ,'{ "homepage": "https://blog.bigsmoke.us/tag/pg_text_semver", "bugtracker": { "web": "https://github.com/bigsmoke/pg_text_semver/issues" }, "repository": { "url": "https://github.com/bigsmoke/pg_text_semver.git", "web": "https://github.com/bigsmoke/pg_text_semver", "type": "git" } }'::jsonb ,'meta-spec' ,'{ "version": "1.0.0", "url": "https://pgxn.org/spec/" }'::jsonb ,'generated_by' ,'`select pg_text_semver_meta_pgxn()`' ,'tags' ,array[ 'domain', 'function', 'functions', 'plpgsql', 'semver', 'settings', 'type' ] ); comment on function pg_text_semver_meta_pgxn() is $md$Returns the JSON meta data that has to go into the `META.json` file needed for PGXN—PostgreSQL Extension Network—packages. The `Makefile` includes a recipe to allow the developer to: `make META.json` to refresh the meta file with the function's current output, including the `default_version`. And indeed, `pg_safer_settings` can be found on PGXN: https://pgxn.org/dist/pg_safer_settings/ $md$; -------------------------------------------------------------------------------------------------------------- create function semver_regexp(with_capture_groups$ bool = false) returns text immutable leakproof parallel safe language sql return $re$(?x) ^ ($re$ || case when with_capture_groups$ then '' else '?:' end || $re$0|[1-9]\d*) # major version \. ($re$ || case when with_capture_groups$ then '' else '?:' end || $re$0|[1-9]\d*) # minor version \. ($re$ || case when with_capture_groups$ then '' else '?:' end || $re$0|[1-9]\d*) # patch version (?:- # pre-release version ($re$ || case when with_capture_groups$ then '' else '?:' end || $re$ (?:0|[1-9]\d*|\d*[a-zA-Z-][0-9a-zA-Z-]*)(?:\.(?:0|[1-9]\d*|\d*[a-zA-Z-][0-9a-zA-Z-]*))* ) )? (?:\+ # build metadata ($re$ || case when with_capture_groups$ then '' else '?:' end || $re$ [0-9a-zA-Z-]+(?:\.[0-9a-zA-Z-]+)* ) )? $ $re$; comment on function semver_regexp(bool) is $md$Returns a regular expression to match or parse a semantic version string. The regular expression is based on the official regular expression from [semver.or](https://semver.org/). $md$; -------------------------------------------------------------------------------------------------------------- create domain semver as text check (value ~ semver_regexp(false)); -------------------------------------------------------------------------------------------------------------- create function semver_prerelease_regexp() returns text immutable leakproof parallel safe return '^(?:0|[1-9]\d*|\d*[a-zA-Z-][0-9a-zA-Z-]*)(?:\.(?:0|[1-9]\d*|\d*[a-zA-Z-][0-9a-zA-Z-]*))*$'; -------------------------------------------------------------------------------------------------------------- create domain semver_prerelease as text check (value ~ semver_prerelease_regexp()); -------------------------------------------------------------------------------------------------------------- create type semver_parsed as ( major text ,minor text ,patch text ,prerelease text ,build text ); comment on type semver_parsed is $md$This composite type can be used to access the individual parts of a `semver`. A semantic version can take 4 forms. Depending on the form, the `prerelease` and/or `build` fields may be `NLL`. `major`, `minor` and `patch` can never be `NULL` for a valid semantic version. | # | Form | Example | `prerelease` | `build` | | -- | ---------------------------------------------- | ----------------- | ------------ | ---------- | | 1. | `..` | 1.0.22 | `NULL` | `NULL` | | 2. | `..-` | 1.0.22-alpha2 | `NOT NULL` | `NULL` | | 3. | `..-+` | 1.0.22-alpha2+arm | `NOT NULL` | `NOT NULL` | | 4. | `..+` | 1.0.22+arm | `NULL` | `NOT NULL` | To unparse/serialize a `semver_parsed` composite values, cast it to `semver` or `text`. $md$; -------------------------------------------------------------------------------------------------------------- create function semver_parsed(text) returns semver_parsed immutable leakproof parallel safe language sql begin atomic select row( (m.match_groups)[1] ,(m.match_groups)[2] ,(m.match_groups)[3] ,(m.match_groups)[4] ,(m.match_groups)[5] )::semver_parsed from ( select regexp_match($1, semver_regexp(true)) as match_groups ) as m ; end; -------------------------------------------------------------------------------------------------------------- create function semver_parsed(semver) returns semver_parsed immutable leakproof parallel safe language sql return semver_parsed($1::text); -------------------------------------------------------------------------------------------------------------- create cast (text as semver_parsed) with function semver_parsed(text) as assignment; -------------------------------------------------------------------------------------------------------------- create function semver(semver_parsed) returns semver immutable leakproof parallel safe language sql return ($1).major || '.' || ($1).minor || '.' || ($1).patch || coalesce('-' || ($1).prerelease, '') || coalesce('+' || ($1).build, ''); -------------------------------------------------------------------------------------------------------------- create cast (semver_parsed as text) with function semver(semver_parsed) as assignment; -------------------------------------------------------------------------------------------------------------- create function semver_eq(semver, semver) returns bool immutable leakproof parallel safe language sql return case when (regexp_match($1, semver_regexp(true)))[1] = (regexp_match($2, semver_regexp(true)))[1] and (regexp_match($1, semver_regexp(true)))[2] = (regexp_match($2, semver_regexp(true)))[2] and (regexp_match($1, semver_regexp(true)))[3] = (regexp_match($2, semver_regexp(true)))[3] and (regexp_match($1, semver_regexp(true)))[4] is not distinct from (regexp_match($2, semver_regexp(true)))[4] then true else false end; comment on function semver_eq(semver, semver) is $md$This functions returns true if two semantic versions are _semantically_ identical to each other, false otherwise. This is the only function in the `semver_(semver, semver)` family that does not utilize `semver_cmp(semver, semver)`. Whether that one extra level of indirection would indeed hinder performance remains to be tested, as of March 2, 2024. $md$; -------------------------------------------------------------------------------------------------------------- create operator = ( leftarg = semver ,rightarg = semver ,function = semver_eq ,negator = != ); -------------------------------------------------------------------------------------------------------------- create function semver_prerelease_cmp(semver_prerelease, semver_prerelease) returns int called on null input immutable leakproof parallel safe language sql return case when $1 is not distinct from $2 then 0 -- “When major, minor, and patch are equal, a pre-release version has lower precedence than -- a normal version." when $1 is null and $2 is not null then 1 when $1 is not null and $2 is null then -1 else coalesce( ( select diff from ( select case -- “A larger set of pre-release fields has a higher precedence than a smaller -- set, if all of the preceding identifiers are equal.” when a.id is not null and b.id is null then 1 when a.id is null and b.id is not null then -1 -- “Numeric identifiers always have lower precedence than non-numeric identifiers.” when a.id ~ '^[0-9]+$' and b.id !~ '^[0-9]+$' then -1 when a.id !~ '^[0-9]+$' and b.id ~ '^[0-9]+$' then 1 -- “Identifiers consisting of only digits are compared numerically.” when a.id ~ '^[0-9]+$' and b.id ~ '^[0-9]+$' then sign(int8(a.id) - int8(b.id)) -- “Identifiers with letters or hyphens are compared lexically in ASCII sort order.” when a.id !~ '^[0-9]+$' and b.id !~ '^[0-9]+$' and a.id < b.id then -1 when a.id !~ '^[0-9]+$' and b.id !~ '^[0-9]+$' and a.id > b.id then 1 else null end as diff ,coalesce(a.pos, b.pos) as pos from string_to_table($1, '.') with ordinality as a(id, pos) full outer join string_to_table($2, '.') with ordinality as b(id, pos) on a.pos = b.pos ) as d where diff is not null and diff != 0 order by pos limit 1 ) ,0 ) end ; -------------------------------------------------------------------------------------------------------------- create function semver_prerelease_eq(semver_prerelease, semver_prerelease) returns bool immutable leakproof parallel safe language sql return case when semver_prerelease_cmp($1, $2) = 0 then true else false end; create operator = ( leftarg = semver_prerelease ,rightarg = semver_prerelease ,function = semver_prerelease_eq ,negator = != ); create function semver_prerelease_gt(semver_prerelease, semver_prerelease) returns bool immutable leakproof parallel safe language sql return case when semver_prerelease_cmp($1, $2) = 1 then true else false end; create operator > ( leftarg = semver_prerelease ,rightarg = semver_prerelease ,function = semver_prerelease_gt ,commutator = < ,negator = <= ); create function semver_prerelease_ge(semver_prerelease, semver_prerelease) returns bool immutable leakproof parallel safe language sql return case when semver_prerelease_cmp($1, $2) >= 0 then true else false end; create operator >= ( leftarg = semver_prerelease ,rightarg = semver_prerelease ,function = semver_prerelease_ge ,commutator = <= ,negator = < ); create function semver_prerelease_lt(semver_prerelease, semver_prerelease) returns bool immutable leakproof parallel safe language sql return case when semver_prerelease_cmp($1, $2) = -1 then true else false end; create operator < ( leftarg = semver_prerelease ,rightarg = semver_prerelease ,function = semver_prerelease_lt ,commutator = > ,negator = >= ); create function semver_prerelease_le(semver_prerelease, semver_prerelease) returns bool immutable leakproof parallel safe language sql return case when semver_prerelease_cmp($1, $2) <= 0 then true else false end; create operator <= ( leftarg = semver_prerelease ,rightarg = semver_prerelease ,function = semver_prerelease_le ,commutator = >= ,negator = > ); -------------------------------------------------------------------------------------------------------------- create function semver_cmp(semver_parsed, semver_parsed) returns int immutable leakproof parallel safe language sql return ( select case when length(($1).major) > length(($2).major) then 1 when length(($1).major) < length(($2).major) then -1 when ($1).major > ($2).major then 1 when ($1).major < ($2).major then -1 when length(($1).minor) > length(($2).minor) then 1 when length(($1).minor) < length(($2).minor) then -1 when ($1).minor > ($2).minor then 1 when ($1).minor < ($2).minor then -1 when length(($1).patch) > length(($2).patch) then 1 when length(($1).patch) < length(($2).patch) then -1 when ($1).patch > ($2).patch then 1 when ($1).patch < ($2).patch then -1 else semver_prerelease_cmp(($1).prerelease, ($2).prerelease) end ); -------------------------------------------------------------------------------------------------------------- create function semver_eq(semver_parsed, semver_parsed) returns bool immutable leakproof parallel safe language sql return case when semver_cmp($1, $2) = 0 then true else false end; create operator = ( leftarg = semver_parsed ,rightarg = semver_parsed ,function = semver_eq ,negator = != ); create function semver_gt(semver_parsed, semver_parsed) returns bool immutable leakproof parallel safe language sql return case when semver_cmp($1, $2) = 1 then true else false end; create operator > ( leftarg = semver_parsed ,rightarg = semver_parsed ,function = semver_gt ,commutator = < ,negator = <= ); create function semver_ge(semver_parsed, semver_parsed) returns bool immutable leakproof parallel safe language sql return case when semver_cmp($1, $2) >= 0 then true else false end; create operator >= ( leftarg = semver_parsed ,rightarg = semver_parsed ,function = semver_ge ,commutator = <= ,negator = < ); create function semver_lt(semver_parsed, semver_parsed) returns bool immutable leakproof parallel safe language sql return case when semver_cmp($1, $2) = -1 then true else false end; create operator < ( leftarg = semver_parsed ,rightarg = semver_parsed ,function = semver_lt ,commutator = > ,negator = >= ); create function semver_le(semver_parsed, semver_parsed) returns bool immutable leakproof parallel safe language sql return case when semver_cmp($1, $2) <= 0 then true else false end; create operator <= ( leftarg = semver_parsed ,rightarg = semver_parsed ,function = semver_le ,commutator = >= ,negator = > ); create operator class text_semver_parsed_ops default for type semver_parsed using btree as operator 1 < , operator 2 <= , operator 3 = , operator 4 >= , operator 5 > , function 1 semver_cmp(semver_parsed, semver_parsed); -------------------------------------------------------------------------------------------------------------- create function semver_cmp(semver, semver) returns int immutable leakproof parallel safe language sql return semver_cmp(semver_parsed($1), semver_parsed($2)); -------------------------------------------------------------------------------------------------------------- create function semver_gt(semver, semver) returns bool immutable leakproof parallel safe language sql return case when semver_cmp($1, $2) = 1 then true else false end; create operator > ( leftarg = semver ,rightarg = semver ,function = semver_gt ,commutator = < ,negator = <= ); create function semver_ge(semver, semver) returns bool immutable leakproof parallel safe language sql return case when semver_cmp($1, $2) >= 0 then true else false end; create operator >= ( leftarg = semver ,rightarg = semver ,function = semver_ge ,commutator = <= ,negator = < ); create function semver_lt(semver, semver) returns bool immutable leakproof parallel safe language sql return case when semver_cmp($1, $2) = -1 then true else false end; create operator < ( leftarg = semver ,rightarg = semver ,function = semver_lt ,commutator = > ,negator = >= ); create function semver_le(semver, semver) returns bool immutable leakproof parallel safe language sql return case when semver_cmp($1, $2) <= 0 then true else false end; create operator <= ( leftarg = semver ,rightarg = semver ,function = semver_le ,commutator = >= ,negator = > ); /* create operator class text_semver_ops default for type semver using btree as operator 1 < , operator 2 <= , operator 3 = , operator 4 >= , operator 5 > , function 1 semver_cmp(semver, semver); */ -------------------------------------------------------------------------------------------------------------- create function semver_greatest(semver, semver) returns semver --returns null on null input immutable leakproof parallel safe language sql return case when $1 is not null and $2 is null then $1 when $1 is null and $2 is not null then $2 when $1 > $2 then $1 else $2 end; -------------------------------------------------------------------------------------------------------------- create aggregate max(semver) ( sfunc = semver_greatest, stype = semver ); -------------------------------------------------------------------------------------------------------------- create function semver_least(semver, semver) returns semver --returns null on null input immutable leakproof parallel safe language sql return case when $1 is not null and $2 is null then $1 when $1 is null and $2 is not null then $2 when $1 < $2 then $1 else $2 end; -------------------------------------------------------------------------------------------------------------- create aggregate min(semver) ( sfunc = semver_least, stype = semver ); -------------------------------------------------------------------------------------------------------------- create procedure test__pg_text_semver() set search_path to pg_catalog set plpgsql.check_asserts to true set pg_readme.include_this_routine_definition to true language plpgsql as $$ begin -- Because this extension is relocatable, let's update the search_path to include the extensions current -- schema (which we couldn't have done by `SET search_path TO CURRENT` in the extension setup script. perform set_config('search_path', e.extnamespace::regnamespace::text || ', pg_catalog', true) from pg_extension as e where e.extname = 'pg_text_semver' ; -- Remember the buildup of a semver: ..-+ -- We can validate the prerelease portion of a semantic version separately: assert 'alpha-1' ~ semver_prerelease_regexp(); assert '0123' !~ semver_prerelease_regexp(), '0 may NOT lead in a numeric prerelease identifer.'; assert '0something' ~ semver_prerelease_regexp(), '0 MAY lead in an ALPHAnumeric prerelease identifer.'; -- Or we can use the domain that uses that regexp in its check constraint: perform 'alpha-1'::semver_prerelease; perform 'alpha28743'::semver_prerelease; perform '0.1.a.a1-.99-e'::semver_prerelease; perform 'a1.99-e-'::semver_prerelease; perform null::semver_prerelease, 'A prerelease tag is not a mandatory part of a semantic version.'; -- -- The prerelease parts of semantic versions can be separately compared: assert semver_prerelease_cmp(null, 'beta') = 1; assert null::semver_prerelease > 'beta'::semver_prerelease; assert semver_prerelease_cmp(null, null) = 0; assert null::semver_prerelease = null::semver_prerelease, 'Missing prerelease tags should count equally.'; assert semver_prerelease_cmp('same', 'same') = 0; assert 'same'::semver_prerelease = 'same'::semver_prerelease; assert 's.4.m.e'::semver_prerelease = 's.4.m.e'::semver_prerelease; assert semver_prerelease_cmp('alpha', null) = -1; assert 'alpha'::semver_prerelease < null::semver_prerelease; assert semver_prerelease_cmp('string.180', 'string.20') = 1; assert 'string.180'::semver_prerelease > 'string.20'::semver_prerelease; assert semver_prerelease_cmp('has.3.identifiers', 'has.3.identifiers.plus-1') = -1; assert 'has.3.identifiers'::semver_prerelease < 'has.3.identifiers.plus-1'::semver_prerelease; assert semver_prerelease_cmp('0.1.2.3.4', '0.1.2.3.4') = 0; assert '0.1.2.3.4'::semver_prerelease = '0.1.2.3.4'::semver_prerelease; assert semver_prerelease_cmp('0.1.2.3.4.5', '0.1.2.3.4') = 1; assert '0.1.2.3.4.5'::semver_prerelease > '0.1.2.3.4'::semver_prerelease; -- -- The `semvver_parsed` type and accompanying functions are used by the `semver` comparison functions, -- so we test the former first because we want to test from the deepest dependency up, which is also -- why we _started_ by testing the `semver_prerelease` domain and its associated functions. -- -- We cast to `jsonb` here to bypass `semver_parsed` its very own equality operator. assert to_jsonb(semver_parsed('1.0.0')) = to_jsonb(row('1', '0', '0', null, null)::semver_parsed); assert to_jsonb(semver_parsed('8.4.7-alpha')) = to_jsonb(row('8', '4', '7', 'alpha', null)::semver_parsed); assert to_jsonb(semver_parsed('1.0.0+commit-x')) = to_jsonb(row('1', '0', '0', null, 'commit-x')::semver_parsed); assert to_jsonb(semver_parsed('1.0.0-a.1+commit-y')) = to_jsonb(row('1', '0', '0', 'a.1', 'commit-y')::semver_parsed); -- Semantic versions can be constructed from a composite by casting to `text`. assert row('1', '0', '0', null, null)::semver_parsed::text = '1.0.0'; -- Or by casting to `semver` (a `text` `DOMAIN`): assert row('1', '0', '0', null, null)::semver_parsed::semver = '1.0.0'; -- When we cast to `semver`, validation is implicit: begin perform row('l', '0', 'l', null, null)::semver_parsed::semver; raise assert_failure using message = 'l.0.l should not be accepted as a valid semver.'; exception when check_violation then end; -- `semver_parsed` can be ordered semantically. declare _presorted text[] = array[ '0.0.9' ,'0.0.88-alpha-2-a' ,'0.0.88+stuff' ,'0.0.88' ,'1.0.0-a' ,'1.0.0-a.123' ,'1.0.0' ]; _shuffled text[] = array[ '0.0.88-alpha-2-a' ,'0.0.88' ,'0.0.9' ,'1.0.0' ,'1.0.0-a.123' ,'1.0.0-a' ,'0.0.88+stuff' ]; _resorted text[] = (select array_agg(v order by v::semver_parsed) from unnest(_shuffled) as v); begin assert _resorted = _presorted, format('%s ≠ %s', _resorted, _presorted); end; assert '0.9.3'::semver < '0.11.2'::semver; assert '10.0.0'::semver > '2.9.9'::semver; assert '1.0.0-alpha'::semver < '1.0.0'::semver; assert '1.0.0-alpha'::semver < '1.0.0-alpha.2'::semver; assert '1.0.0-alpha.1'::semver < '1.0.0-alpha.2'::semver; assert '1.0.0-alpha.1'::semver < '1.0.0-alpha.a'::semver; assert '1.0.0-alpha'::semver < '1.0.0-beta'::semver; assert '8.8.8+bla'::semver = '8.8.8'::semver, '“Build metadata MUST be ignored when determining version precedence.”'; assert '8.8.8+bla'::semver = '8.8.8+bah'::semver, '“Build metadata MUST be ignored when determining version precedence.”'; assert '8.8.8-alpha+build12'::semver = '8.8.8-alpha+build13'::semver, '“Build metadata MUST be ignored when determining version precedence.”'; assert '8.8.8-alpha+build12'::semver = '8.8.8-alpha'::semver; -- Unlike `semver_parsed`, plain `semver` cannot be ordered semantically; because `semver` is a `text` -- domain, it will be sorted lexically as such. _But_, we can always cast to `semver_parsed` for -- ordering and indexing. Do remember to index on that cast expression then, or your queries will be -- slow! declare _presorted text[] := array[ '0.0.9' ,'0.0.88-alpha-2-a' ,'0.0.88+stuff' ,'0.0.88' ,'1.0.0-a' ,'1.0.0-a.123' ,'1.0.0' ]; _shuffled text[] := array[ '0.0.88-alpha-2-a' ,'0.0.88' ,'0.0.9' ,'1.0.0' ,'1.0.0-a.123' ,'1.0.0-a' ,'0.0.88+stuff' ]; _resorted text[]; begin create temporary table shuffled (v semver); insert into shuffled select v from unnest(_shuffled) as v; --create index on shuffled (v text_semver_ops); -- Operator classes for domains are ignored. create index on shuffled ((v::semver_parsed)); _resorted := (select array_agg(v order by v::semver_parsed) from shuffled); assert _resorted = _presorted, format('%s ≠ %s', _resorted, _presorted); end; declare _shuffled text[] := array[ '0.0.88' ,'1.0.0+stuff' ,'1.0.0' ,'1.0.0-a' ,'0.0.88+stuff' ,'0.0.88-alpha-2-a' ,'1.0.0-a.123' ]; begin -- `min(semver)` and `max(semver)` are also possible: assert (select min(v::semver) from unnest(_shuffled) as v)::text = '0.0.88-alpha-2-a'; assert (select max(v::semver) from unnest(_shuffled) as v)::text in ('1.0.0', '1.0.0+stuff'); end; perform null::semver; raise transaction_rollback; exception when transaction_rollback then end; $$; --------------------------------------------------------------------------------------------------------------