Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

Commit 794f10f

Browse filesBrowse files
peterepetere
committed
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
1 parent d56cb42 commit 794f10f
Copy full SHA for 794f10f

File tree

Expand file treeCollapse file tree

7 files changed

+157
-1
lines changed
Filter options
Expand file treeCollapse file tree

7 files changed

+157
-1
lines changed

‎doc/src/sgml/func.sgml

Copy file name to clipboardExpand all lines: doc/src/sgml/func.sgml
+30Lines changed: 30 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -14127,6 +14127,14 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple
1412714127
<primary>gen_random_uuid</primary>
1412814128
</indexterm>
1412914129

14130+
<indexterm>
14131+
<primary>uuid_extract_timestamp</primary>
14132+
</indexterm>
14133+
14134+
<indexterm>
14135+
<primary>uuid_extract_version</primary>
14136+
</indexterm>
14137+
1413014138
<para>
1413114139
<productname>PostgreSQL</productname> includes one function to generate a UUID:
1413214140
<synopsis>
@@ -14141,6 +14149,28 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple
1414114149
implement other standard algorithms for generating UUIDs.
1414214150
</para>
1414314151

14152+
<para>
14153+
There are also functions to extract data from UUIDs:
14154+
<synopsis>
14155+
<function>uuid_extract_timestamp</function> (uuid) <returnvalue>timestamp with time zone</returnvalue>
14156+
</synopsis>
14157+
This function extracts a <type>timestamp with time zone</type> from UUID
14158+
version 1. For other versions, this function returns null. Note that the
14159+
extracted timestamp is not necessarily exactly equal to the time the UUID
14160+
was generated; this depends on the implementation that generated the UUID.
14161+
</para>
14162+
14163+
<para>
14164+
<synopsis>
14165+
<function>uuid_extract_version</function> (uuid) <returnvalue>smallint</returnvalue>
14166+
</synopsis>
14167+
This function extracts the version from a UUID of the variant described by
14168+
<ulink url="https://tools.ietf.org/html/rfc4122">RFC 4122</ulink>. For
14169+
other variants, this function returns null. For example, for a UUID
14170+
generated by <function>gen_random_uuid</function>, this function will
14171+
return 4.
14172+
</para>
14173+
1414414174
<para>
1414514175
<productname>PostgreSQL</productname> also provides the usual comparison
1414614176
operators shown in <xref linkend="functions-comparison-op-table"/> for

‎src/backend/utils/adt/uuid.c

Copy file name to clipboardExpand all lines: src/backend/utils/adt/uuid.c
+64Lines changed: 64 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -20,6 +20,7 @@
2020
#include "utils/fmgrprotos.h"
2121
#include "utils/guc.h"
2222
#include "utils/sortsupport.h"
23+
#include "utils/timestamp.h"
2324
#include "utils/uuid.h"
2425

2526
/* sortsupport for uuid */
@@ -425,3 +426,66 @@ gen_random_uuid(PG_FUNCTION_ARGS)
425426

426427
PG_RETURN_UUID_P(uuid);
427428
}
429+
430+
#define UUIDV1_EPOCH_JDATE 2299161 /* == date2j(1582,10,15) */
431+
432+
/*
433+
* Extract timestamp from UUID.
434+
*
435+
* Returns null if not RFC 4122 variant or not a version that has a timestamp.
436+
*/
437+
Datum
438+
uuid_extract_timestamp(PG_FUNCTION_ARGS)
439+
{
440+
pg_uuid_t *uuid = PG_GETARG_UUID_P(0);
441+
int version;
442+
uint64 tms;
443+
TimestampTz ts;
444+
445+
/* check if RFC 4122 variant */
446+
if ((uuid->data[8] & 0xc0) != 0x80)
447+
PG_RETURN_NULL();
448+
449+
version = uuid->data[6] >> 4;
450+
451+
if (version == 1)
452+
{
453+
tms = ((uint64) uuid->data[0] << 24)
454+
+ ((uint64) uuid->data[1] << 16)
455+
+ ((uint64) uuid->data[2] << 8)
456+
+ ((uint64) uuid->data[3])
457+
+ ((uint64) uuid->data[4] << 40)
458+
+ ((uint64) uuid->data[5] << 32)
459+
+ (((uint64) uuid->data[6] & 0xf) << 56)
460+
+ ((uint64) uuid->data[7] << 48);
461+
462+
/* convert 100-ns intervals to us, then adjust */
463+
ts = (TimestampTz) (tms / 10) -
464+
((uint64) POSTGRES_EPOCH_JDATE - UUIDV1_EPOCH_JDATE) * SECS_PER_DAY * USECS_PER_SEC;
465+
466+
PG_RETURN_TIMESTAMPTZ(ts);
467+
}
468+
469+
/* not a timestamp-containing UUID version */
470+
PG_RETURN_NULL();
471+
}
472+
473+
/*
474+
* Extract UUID version.
475+
*
476+
* Returns null if not RFC 4122 variant.
477+
*/
478+
Datum
479+
uuid_extract_version(PG_FUNCTION_ARGS)
480+
{
481+
pg_uuid_t *uuid = PG_GETARG_UUID_P(0);
482+
uint16 version;
483+
484+
/* check if RFC 4122 variant */
485+
if ((uuid->data[8] & 0xc0) != 0x80)
486+
PG_RETURN_NULL();
487+
488+
version = uuid->data[6] >> 4;
489+
490+
PG_RETURN_UINT16(version);
491+
}

‎src/include/catalog/catversion.h

Copy file name to clipboardExpand all lines: src/include/catalog/catversion.h
+1-1Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -57,6 +57,6 @@
5757
*/
5858

5959
/* yyyymmddN */
60-
#define CATALOG_VERSION_NO 202403181
60+
#define CATALOG_VERSION_NO 202403191
6161

6262
#endif

‎src/include/catalog/pg_proc.dat

Copy file name to clipboardExpand all lines: src/include/catalog/pg_proc.dat
+7Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -9170,6 +9170,13 @@
91709170
{ oid => '3432', descr => 'generate random UUID',
91719171
proname => 'gen_random_uuid', proleakproof => 't', provolatile => 'v',
91729172
prorettype => 'uuid', proargtypes => '', prosrc => 'gen_random_uuid' },
9173+
{ oid => '9897', descr => 'extract timestamp from UUID',
9174+
proname => 'uuid_extract_timestamp', proleakproof => 't',
9175+
prorettype => 'timestamptz', proargtypes => 'uuid',
9176+
prosrc => 'uuid_extract_timestamp' },
9177+
{ oid => '9898', descr => 'extract version from RFC 4122 UUID',
9178+
proname => 'uuid_extract_version', proleakproof => 't', prorettype => 'int2',
9179+
proargtypes => 'uuid', prosrc => 'uuid_extract_version' },
91739180

91749181
# pg_lsn
91759182
{ oid => '3229', descr => 'I/O',

‎src/test/regress/expected/opr_sanity.out

Copy file name to clipboardExpand all lines: src/test/regress/expected/opr_sanity.out
+2Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -872,6 +872,8 @@ xid8ge(xid8,xid8)
872872
xid8eq(xid8,xid8)
873873
xid8ne(xid8,xid8)
874874
xid8cmp(xid8,xid8)
875+
uuid_extract_timestamp(uuid)
876+
uuid_extract_version(uuid)
875877
-- restore normal output mode
876878
\a\t
877879
-- List of functions used by libpq's fe-lobj.c

‎src/test/regress/expected/uuid.out

Copy file name to clipboardExpand all lines: src/test/regress/expected/uuid.out
+39Lines changed: 39 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -168,5 +168,44 @@ SELECT count(DISTINCT guid_field) FROM guid1;
168168
2
169169
(1 row)
170170

171+
-- extract functions
172+
-- version
173+
SELECT uuid_extract_version('11111111-1111-5111-8111-111111111111'); -- 5
174+
uuid_extract_version
175+
----------------------
176+
5
177+
(1 row)
178+
179+
SELECT uuid_extract_version(gen_random_uuid()); -- 4
180+
uuid_extract_version
181+
----------------------
182+
4
183+
(1 row)
184+
185+
SELECT uuid_extract_version('11111111-1111-1111-1111-111111111111'); -- null
186+
uuid_extract_version
187+
----------------------
188+
189+
(1 row)
190+
191+
-- timestamp
192+
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
193+
?column?
194+
----------
195+
t
196+
(1 row)
197+
198+
SELECT uuid_extract_timestamp(gen_random_uuid()); -- null
199+
uuid_extract_timestamp
200+
------------------------
201+
202+
(1 row)
203+
204+
SELECT uuid_extract_timestamp('11111111-1111-1111-1111-111111111111'); -- null
205+
uuid_extract_timestamp
206+
------------------------
207+
208+
(1 row)
209+
171210
-- clean up
172211
DROP TABLE guid1, guid2 CASCADE;

‎src/test/regress/sql/uuid.sql

Copy file name to clipboardExpand all lines: src/test/regress/sql/uuid.sql
+14Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -85,5 +85,19 @@ INSERT INTO guid1 (guid_field) VALUES (gen_random_uuid());
8585
INSERT INTO guid1 (guid_field) VALUES (gen_random_uuid());
8686
SELECT count(DISTINCT guid_field) FROM guid1;
8787

88+
89+
-- extract functions
90+
91+
-- version
92+
SELECT uuid_extract_version('11111111-1111-5111-8111-111111111111'); -- 5
93+
SELECT uuid_extract_version(gen_random_uuid()); -- 4
94+
SELECT uuid_extract_version('11111111-1111-1111-1111-111111111111'); -- null
95+
96+
-- timestamp
97+
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
98+
SELECT uuid_extract_timestamp(gen_random_uuid()); -- null
99+
SELECT uuid_extract_timestamp('11111111-1111-1111-1111-111111111111'); -- null
100+
101+
88102
-- clean up
89103
DROP TABLE guid1, guid2 CASCADE;

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.