Add some UUID support functions

Add uuid_extract_timestamp() and uuid_extract_version().

Author: Andrey Borodin
Reviewed-by: Sergey Prokhorenko, Kirk Wolak, Przemysław Sztoch
Reviewed-by: Nikolay Samokhvalov, Jelte Fennema-Nio, Aleksander Alekseev
Reviewed-by: Peter Eisentraut, Chris Travers, Lukas Fittl
Discussion: https://postgr.es/m/CAAhFRxitJv%3DyoGnXUgeLB_O%2BM7J2BJAmb5jqAT9gZ3bij3uLDA%40mail.gmail.com

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 3c52d90d3ab6926e84775dcacdf73d6e102ba1b6..5b225ccf4f5f14f738036deab06b71ca91d872c7 100644 (file)
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -14127,6 +14127,14 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple
    <primary>gen_random_uuid</primary>
   </indexterm>
 
+  <indexterm>
+   <primary>uuid_extract_timestamp</primary>
+  </indexterm>
+
+  <indexterm>
+   <primary>uuid_extract_version</primary>
+  </indexterm>
+
   <para>
    <productname>PostgreSQL</productname> includes one function to generate a UUID:
 <synopsis>
@@ -14141,6 +14149,28 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple
    implement other standard algorithms for generating UUIDs.
   </para>
 
+  <para>
+   There are also functions to extract data from UUIDs:
+<synopsis>
+<function>uuid_extract_timestamp</function> (uuid) <returnvalue>timestamp with time zone</returnvalue>
+</synopsis>
+   This function extracts a <type>timestamp with time zone</type> from UUID
+   version 1.  For other versions, this function returns null.  Note that the
+   extracted timestamp is not necessarily exactly equal to the time the UUID
+   was generated; this depends on the implementation that generated the UUID.
+  </para>
+
+  <para>
+<synopsis>
+<function>uuid_extract_version</function> (uuid) <returnvalue>smallint</returnvalue>
+</synopsis>
+   This function extracts the version from a UUID of the variant described by
+   <ulink url="https://tools.ietf.org/html/rfc4122">RFC 4122</ulink>.  For
+   other variants, this function returns null.  For example, for a UUID
+   generated by <function>gen_random_uuid</function>, this function will
+   return 4.
+  </para>
+
   <para>
    <productname>PostgreSQL</productname> also provides the usual comparison
    operators shown in <xref linkend="functions-comparison-op-table"/> for

diff --git a/src/backend/utils/adt/uuid.c b/src/backend/utils/adt/uuid.c
index e9c1ec6153786c99de83bed689d058c46502a8b7..45eb1b2fea9f86dba85ca12657bd500c04a00f6f 100644 (file)
--- a/src/backend/utils/adt/uuid.c
+++ b/src/backend/utils/adt/uuid.c
@@ -20,6 +20,7 @@
 #include "utils/fmgrprotos.h"
 #include "utils/guc.h"
 #include "utils/sortsupport.h"
+#include "utils/timestamp.h"
 #include "utils/uuid.h"
 
 /* sortsupport for uuid */
@@ -425,3 +426,66 @@ gen_random_uuid(PG_FUNCTION_ARGS)
 
    PG_RETURN_UUID_P(uuid);
 }
+
+#define UUIDV1_EPOCH_JDATE  2299161 /* == date2j(1582,10,15) */
+
+/*
+ * Extract timestamp from UUID.
+ *
+ * Returns null if not RFC 4122 variant or not a version that has a timestamp.
+ */
+Datum
+uuid_extract_timestamp(PG_FUNCTION_ARGS)
+{
+   pg_uuid_t  *uuid = PG_GETARG_UUID_P(0);
+   int         version;
+   uint64      tms;
+   TimestampTz ts;
+
+   /* check if RFC 4122 variant */
+   if ((uuid->data[8] & 0xc0) != 0x80)
+       PG_RETURN_NULL();
+
+   version = uuid->data[6] >> 4;
+
+   if (version == 1)
+   {
+       tms = ((uint64) uuid->data[0] << 24)
+           + ((uint64) uuid->data[1] << 16)
+           + ((uint64) uuid->data[2] << 8)
+           + ((uint64) uuid->data[3])
+           + ((uint64) uuid->data[4] << 40)
+           + ((uint64) uuid->data[5] << 32)
+           + (((uint64) uuid->data[6] & 0xf) << 56)
+           + ((uint64) uuid->data[7] << 48);
+
+       /* convert 100-ns intervals to us, then adjust */
+       ts = (TimestampTz) (tms / 10) -
+           ((uint64) POSTGRES_EPOCH_JDATE - UUIDV1_EPOCH_JDATE) * SECS_PER_DAY * USECS_PER_SEC;
+
+       PG_RETURN_TIMESTAMPTZ(ts);
+   }
+
+   /* not a timestamp-containing UUID version */
+   PG_RETURN_NULL();
+}
+
+/*
+ * Extract UUID version.
+ *
+ * Returns null if not RFC 4122 variant.
+ */
+Datum
+uuid_extract_version(PG_FUNCTION_ARGS)
+{
+   pg_uuid_t  *uuid = PG_GETARG_UUID_P(0);
+   uint16      version;
+
+   /* check if RFC 4122 variant */
+   if ((uuid->data[8] & 0xc0) != 0x80)
+       PG_RETURN_NULL();
+
+   version = uuid->data[6] >> 4;
+
+   PG_RETURN_UINT16(version);
+}

diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index 6fb22007ed007624b3e5af5df19128f3ee390132..295560a7ffd595bce7376ac2fb653ca9d92a7e00 100644 (file)
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -57,6 +57,6 @@
  */
 
 /*                         yyyymmddN */
-#define CATALOG_VERSION_NO 202403181
+#define CATALOG_VERSION_NO 202403191
 
 #endif

diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 700f7daf7b29c3d2e8b2f52f666a849d494cd569..177d81a891c3341ddd8722b2cad6094ba2a5ab1c 100644 (file)
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -9170,6 +9170,13 @@
 { oid => '3432', descr => 'generate random UUID',
   proname => 'gen_random_uuid', proleakproof => 't', provolatile => 'v',
   prorettype => 'uuid', proargtypes => '', prosrc => 'gen_random_uuid' },
+{ oid => '9897', descr => 'extract timestamp from UUID',
+  proname => 'uuid_extract_timestamp', proleakproof => 't',
+  prorettype => 'timestamptz', proargtypes => 'uuid',
+  prosrc => 'uuid_extract_timestamp' },
+{ oid => '9898', descr => 'extract version from RFC 4122 UUID',
+  proname => 'uuid_extract_version', proleakproof => 't', prorettype => 'int2',
+  proargtypes => 'uuid', prosrc => 'uuid_extract_version' },
 
 # pg_lsn
 { oid => '3229', descr => 'I/O',

diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out
index 7610b011d686fd44f91c99d9fe9931b2023743cf..9d047b21b88ee165c06770ac55aeae59682990d4 100644 (file)
--- a/src/test/regress/expected/opr_sanity.out
+++ b/src/test/regress/expected/opr_sanity.out
@@ -872,6 +872,8 @@ xid8ge(xid8,xid8)
 xid8eq(xid8,xid8)
 xid8ne(xid8,xid8)
 xid8cmp(xid8,xid8)
+uuid_extract_timestamp(uuid)
+uuid_extract_version(uuid)
 -- restore normal output mode
 \a\t
 -- List of functions used by libpq's fe-lobj.c

diff --git a/src/test/regress/expected/uuid.out b/src/test/regress/expected/uuid.out
index 8e7f21910d686e3ef9eb274fc8a5def72856ef95..6026e15ed31977a44b8296a69455463dcc46cad2 100644 (file)
--- a/src/test/regress/expected/uuid.out
+++ b/src/test/regress/expected/uuid.out
@@ -168,5 +168,44 @@ SELECT count(DISTINCT guid_field) FROM guid1;
      2
 (1 row)
 
+-- extract functions
+-- version
+SELECT uuid_extract_version('11111111-1111-5111-8111-111111111111');  -- 5
+ uuid_extract_version 
+----------------------
+                    5
+(1 row)
+
+SELECT uuid_extract_version(gen_random_uuid());  -- 4
+ uuid_extract_version 
+----------------------
+                    4
+(1 row)
+
+SELECT uuid_extract_version('11111111-1111-1111-1111-111111111111');  -- null
+ uuid_extract_version 
+----------------------
+                     
+(1 row)
+
+-- timestamp
+SELECT uuid_extract_timestamp('C232AB00-9414-11EC-B3C8-9F6BDECED846') = 'Tuesday, February 22, 2022 2:22:22.00 PM GMT+05:00';  -- RFC 4122bis test vector
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT uuid_extract_timestamp(gen_random_uuid());  -- null
+ uuid_extract_timestamp 
+------------------------
+ 
+(1 row)
+
+SELECT uuid_extract_timestamp('11111111-1111-1111-1111-111111111111');  -- null
+ uuid_extract_timestamp 
+------------------------
+ 
+(1 row)
+
 -- clean up
 DROP TABLE guid1, guid2 CASCADE;

diff --git a/src/test/regress/sql/uuid.sql b/src/test/regress/sql/uuid.sql
index 9a8f437c7d2911f2d1a5c34b2ec20a82d215135e..c88f6d087a716189d621b0313be2ded63ea94744 100644 (file)
--- a/src/test/regress/sql/uuid.sql
+++ b/src/test/regress/sql/uuid.sql
@@ -85,5 +85,19 @@ INSERT INTO guid1 (guid_field) VALUES (gen_random_uuid());
 INSERT INTO guid1 (guid_field) VALUES (gen_random_uuid());
 SELECT count(DISTINCT guid_field) FROM guid1;
 
+
+-- extract functions
+
+-- version
+SELECT uuid_extract_version('11111111-1111-5111-8111-111111111111');  -- 5
+SELECT uuid_extract_version(gen_random_uuid());  -- 4
+SELECT uuid_extract_version('11111111-1111-1111-1111-111111111111');  -- null
+
+-- timestamp
+SELECT uuid_extract_timestamp('C232AB00-9414-11EC-B3C8-9F6BDECED846') = 'Tuesday, February 22, 2022 2:22:22.00 PM GMT+05:00';  -- RFC 4122bis test vector
+SELECT uuid_extract_timestamp(gen_random_uuid());  -- null
+SELECT uuid_extract_timestamp('11111111-1111-1111-1111-111111111111');  -- null
+
+
 -- clean up
 DROP TABLE guid1, guid2 CASCADE;