Initial version (seems to work, tested with 9.2).

diff --git a/.gitignore b/.gitignore
new file mode 100644 (file)
index 0000000..157d706
--- /dev/null
+++ b/.gitignore
@@ -0,0 +1,2 @@
+timeuuid.o
+timeuuid.so

diff --git a/makefile b/makefile
new file mode 100644 (file)
index 0000000..39bafad
--- /dev/null
+++ b/makefile
@@ -0,0 +1,8 @@
+MODULE_big = timeuuid
+EXTENSION = timeuuid
+DATA = timeuuid--1.0.sql
+OBJS = timeuuid.o
+
+PG_CONFIG = pg_config
+PGXS := $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)

diff --git a/timeuuid--1.0.sql b/timeuuid--1.0.sql
new file mode 100644 (file)
index 0000000..119f771
--- /dev/null
+++ b/timeuuid--1.0.sql
@@ -0,0 +1,129 @@
+\echo Use "CREATE EXTENSION timeuuid" to load this file. \quit
+
+create type timeuuid;
+
+create or replace function timeuuid_in(cstring) returns timeuuid
+    as 'uuid_in' language internal immutable strict;
+create or replace function timeuuid_out(timeuuid) returns cstring
+    as 'uuid_out' language internal immutable strict;
+
+create or replace function timeuuid_recv(internal) returns timeuuid
+    as 'uuid_recv' language internal immutable strict;
+create or replace function timeuuid_send(timeuuid) returns bytea
+    as 'uuid_send' language internal immutable strict;
+
+create type timeuuid (
+    input = timeuuid_in,
+    output = timeuuid_out,
+    receive = timeuuid_recv,
+    send = timeuuid_send,
+    like = uuid
+);
+
+create or replace function timeuuid_eq(timeuuid, timeuuid) returns boolean
+    as 'uuid_eq' language internal immutable strict;
+create or replace function timeuuid_neq(timeuuid, timeuuid) returns boolean
+    as 'uuid_ne' language internal immutable strict;
+
+create or replace function timeuuid_hash(timeuuid) returns int4
+    as 'uuid_hash' language internal immutable strict;
+
+create function timeuuid_lt(timeuuid, timeuuid) returns boolean
+    as 'MODULE_PATHNAME' language c strict immutable;
+create function timeuuid_gt(timeuuid, timeuuid) returns boolean
+    as 'MODULE_PATHNAME' language c strict immutable;
+create function timeuuid_le(timeuuid, timeuuid) returns boolean
+    as 'MODULE_PATHNAME' language c strict immutable;
+create function timeuuid_ge(timeuuid, timeuuid) returns boolean
+    as 'MODULE_PATHNAME' language c strict immutable;
+
+create function timeuuid_cmp(timeuuid, timeuuid) returns int4
+    as 'MODULE_PATHNAME' language c strict immutable;
+
+create operator = (
+    leftarg = timeuuid,
+    rightarg = timeuuid,
+    commutator = =,
+    negator = <>,
+    procedure = timeuuid_eq,
+    restrict = eqsel,
+    join = eqjoinsel,
+    hashes,
+    merges
+);
+
+create operator <> (
+    leftarg = timeuuid,
+    rightarg = timeuuid,
+    commutator = <>,
+    negator = =,
+    procedure = timeuuid_neq,
+    restrict = neqsel,
+    join = neqjoinsel
+);
+
+create operator < (
+    leftarg = timeuuid,
+    rightarg = timeuuid,
+    commutator = >,
+    negator = >=,
+    procedure = timeuuid_lt,
+    restrict = scalarltsel,
+    join = scalarltjoinsel
+);
+
+create operator > (
+    leftarg = timeuuid,
+    rightarg = timeuuid,
+    commutator = <,
+    negator = <=,
+    procedure = timeuuid_gt,
+    restrict = scalargtsel,
+    join = scalargtjoinsel
+);
+
+create operator <= (
+    leftarg = timeuuid,
+    rightarg = timeuuid,
+    commutator = >=,
+    negator = >,
+    procedure = timeuuid_le,
+    restrict = scalarltsel,
+    join = scalarltjoinsel
+);
+
+create operator >= (
+    leftarg = timeuuid,
+    rightarg = timeuuid,
+    commutator = <=,
+    negator = <,
+    procedure = timeuuid_ge,
+    restrict = scalargtsel,
+    join = scalargtjoinsel
+);
+
+create operator class timeuuid_ops
+default for type timeuuid using btree as
+    operator 1 <,
+    operator 2 <=,
+    operator 3 =,
+    operator 4 >=,
+    operator 5 >,
+    function 1 timeuuid_cmp(timeuuid, timeuuid);
+
+create cast (timeuuid as uuid)
+    without function as assignment;
+create cast (uuid as timeuuid)
+    without function as assignment;
+
+create function timeuuid_to_timestamptz(uuid) returns timestamptz
+    as 'MODULE_PATHNAME' language c strict immutable;
+create function timeuuid_to_timestamptz(timeuuid) returns timestamptz
+    as 'MODULE_PATHNAME' language c strict immutable;
+create function timestamptz_to_timeuuid(timestamptz) returns timeuuid
+    as 'MODULE_PATHNAME' language c strict immutable;
+
+create cast (timeuuid as timestamptz)
+    with function timeuuid_to_timestamptz(timeuuid);
+create cast (timestamptz as timeuuid)
+    with function timestamptz_to_timeuuid(timestamptz);

diff --git a/timeuuid.c b/timeuuid.c
new file mode 100644 (file)
index 0000000..8c78c6b
--- /dev/null
+++ b/timeuuid.c
@@ -0,0 +1,107 @@
+#include "postgres.h"
+#include "fmgr.h"
+
+#include "utils/timestamp.h"
+#include "utils/uuid.h"
+
+PG_MODULE_MAGIC;
+
+#define UUID_EPOCH_JDATE 2299161 /* == date2j(1582, 10, 15) */
+
+struct pg_uuid_t {
+    unsigned char data[UUID_LEN];
+};
+
+__attribute__((__packed__))
+struct timeuuid_t {
+    uint32_t time_low;
+    uint16_t time_mid;
+    uint16_t time_hi_and_version;
+    uint8_t data[8];
+};
+
+typedef struct timeuuid_t timeuuid_t;
+
+Datum timeuuid_lt(PG_FUNCTION_ARGS);
+Datum timeuuid_gt(PG_FUNCTION_ARGS);
+Datum timeuuid_le(PG_FUNCTION_ARGS);
+Datum timeuuid_ge(PG_FUNCTION_ARGS);
+
+PG_FUNCTION_INFO_V1(timeuuid_lt);
+PG_FUNCTION_INFO_V1(timeuuid_gt);
+PG_FUNCTION_INFO_V1(timeuuid_le);
+PG_FUNCTION_INFO_V1(timeuuid_ge);
+
+Datum timeuuid_cmp(PG_FUNCTION_ARGS);
+PG_FUNCTION_INFO_V1(timeuuid_cmp);
+
+static int timeuuid_internal_cmp(const pg_uuid_t *arg1, const pg_uuid_t *arg2) {
+    int result;
+    if ((result = memcmp(&arg1->data[6], &arg2->data[6], 2)) != 0)
+        return result;
+    if ((result = memcmp(&arg1->data[4], &arg2->data[4], 2)) != 0)
+        return result;
+    if ((result = memcmp(&arg1->data[0], &arg2->data[0], 4)) != 0)
+        return result;
+    return memcmp(&arg1->data[8], &arg2->data[8], 8);
+}
+
+Datum timeuuid_lt(PG_FUNCTION_ARGS) {
+    pg_uuid_t *arg1 = PG_GETARG_UUID_P(0);
+    pg_uuid_t *arg2 = PG_GETARG_UUID_P(1);
+    PG_RETURN_BOOL(timeuuid_internal_cmp(arg1, arg2) < 0);
+}
+
+Datum timeuuid_gt(PG_FUNCTION_ARGS) {
+    pg_uuid_t *arg1 = PG_GETARG_UUID_P(0);
+    pg_uuid_t *arg2 = PG_GETARG_UUID_P(1);
+    PG_RETURN_BOOL(timeuuid_internal_cmp(arg1, arg2) > 0);
+}
+
+Datum timeuuid_le(PG_FUNCTION_ARGS) {
+    pg_uuid_t *arg1 = PG_GETARG_UUID_P(0);
+    pg_uuid_t *arg2 = PG_GETARG_UUID_P(1);
+    PG_RETURN_BOOL(timeuuid_internal_cmp(arg1, arg2) <= 0);
+}
+
+Datum timeuuid_ge(PG_FUNCTION_ARGS) {
+    pg_uuid_t *arg1 = PG_GETARG_UUID_P(0);
+    pg_uuid_t *arg2 = PG_GETARG_UUID_P(1);
+    PG_RETURN_BOOL(timeuuid_internal_cmp(arg1, arg2) >= 0);
+}
+
+Datum timeuuid_cmp(PG_FUNCTION_ARGS) {
+    pg_uuid_t *arg1 = PG_GETARG_UUID_P(0);
+    pg_uuid_t *arg2 = PG_GETARG_UUID_P(1);
+    PG_RETURN_INT32(timeuuid_internal_cmp(arg1, arg2));
+}
+
+#ifdef HAVE_INT64_TIMESTAMP
+Datum timeuuid_to_timestamptz(PG_FUNCTION_ARGS);
+Datum timestamptz_to_timeuuid(PG_FUNCTION_ARGS);
+
+PG_FUNCTION_INFO_V1(timeuuid_to_timestamptz);
+PG_FUNCTION_INFO_V1(timestamptz_to_timeuuid);
+
+static const int64_t uuid_diff = ((int64_t) POSTGRES_EPOCH_JDATE - (int64_t) UUID_EPOCH_JDATE) * SECS_PER_DAY * USECS_PER_SEC;
+
+Datum timeuuid_to_timestamptz(PG_FUNCTION_ARGS) {
+    timeuuid_t *uuid = (timeuuid_t *) PG_GETARG_UUID_P(0);
+    TimestampTz ts = (((uint64_t) ntohs(uuid->time_hi_and_version) & 0xfff) << 48) + ((uint64_t) ntohs(uuid->time_mid) << 32) + (uint64_t) ntohl(uuid->time_low);
+    ts = (int64_t) (ts / 10) - uuid_diff;
+    PG_RETURN_TIMESTAMPTZ(ts);
+}
+
+Datum timestamptz_to_timeuuid(PG_FUNCTION_ARGS) {
+    TimestampTz ts = PG_GETARG_TIMESTAMPTZ(0);
+    ts = (ts + uuid_diff) * 10;
+    timeuuid_t *uuid = (timeuuid_t *) palloc(sizeof(*uuid));
+    uuid->time_hi_and_version = htons((ts >> 48) | (0x1 << 12));
+    uuid->time_mid = htons(ts >> 32);
+    uuid->time_low = htonl(ts);
+    memset(uuid->data, 0, 8);
+    PG_RETURN_UUID_P(uuid);
+}
+#else
+#error
+#endif

diff --git a/timeuuid.control b/timeuuid.control
new file mode 100644 (file)
index 0000000..c12bf25
--- /dev/null
+++ b/timeuuid.control
@@ -0,0 +1,5 @@
+# seg extension
+comment = 'data type for time-based v1 uuids: the perfect balance of bigserial and timestamptz'
+default_version = '1.0'
+module_pathname = '$libdir/timeuuid'
+relocatable = true