Movatterモバイル変換

[0]ホーム
URL:

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

 Sign up
Appearance settings

Commit794f10f

Browse files
committed
Add some UUID support functions
Add uuid_extract_timestamp() and uuid_extract_version().Author: Andrey BorodinReviewed-by: Sergey Prokhorenko, Kirk Wolak, Przemysław SztochReviewed-by: Nikolay Samokhvalov, Jelte Fennema-Nio, Aleksander AlekseevReviewed-by: Peter Eisentraut, Chris Travers, Lukas FittlDiscussion:https://postgr.es/m/CAAhFRxitJv%3DyoGnXUgeLB_O%2BM7J2BJAmb5jqAT9gZ3bij3uLDA%40mail.gmail.com
1 parentd56cb42 commit794f10f

File tree

7 files changed

+157
-1
lines changed

7 files changed

+157
-1
lines changed

‎doc/src/sgml/func.sgml

Lines 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

Lines 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+
#defineUUIDV1_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+
intversion;
442+
uint64tms;
443+
TimestampTzts;
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+
uint16version;
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

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -57,6 +57,6 @@
5757
*/
5858

5959
/*yyyymmddN */
60-
#defineCATALOG_VERSION_NO202403181
60+
#defineCATALOG_VERSION_NO202403191
6161

6262
#endif

‎src/include/catalog/pg_proc.dat

Lines 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

Lines 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

Lines 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

Lines 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
SELECTcount(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
DROPTABLE guid1, guid2 CASCADE;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp