I would like to create a new type for version strings that sorts numerically. The composite type below was quick to write and does not require superuser privileges. However, it doesn't respond to type casts the way I'd like.
Is there a way to implement this type's literal conversion without resorting to a base type?
=> CREATE TYPE myversion AS (version integer[], build varchar);
CREATE TYPE
=> CREATE OR REPLACE FUNCTION myversion(text)
-> RETURNS myversion AS $$
$> SELECT
$> string_to_array(split_part($1, '+', 1), '.')::integer[],
$> split_part($1, '+', 2);
$> $$ LANGUAGE SQL IMMUTABLE STRICT;
CREATE FUNCTION
=> SELECT myversion('1.0.9+jenkins');
myversion
---------------------
("{1,0,9}",jenkins)
(1 row)
=> SELECT '1.0.9+jenkins'::myversion;
ERROR: malformed record literal: "1.0.9+jenkins"
LINE 1: SELECT '1.0.9+jenkins'::myversion;
^
DETAIL: Missing left parenthesis.