This extension for PostgreSQL provides some useful functions that aim at speeding up various tasks especially with version 1 UUID's implemented in compliance to RFC 4122.
The function uuid_version(uuid)
returns the version field of the UUID as
specified in RFC 4122, e.g.:
SELECT uuid_version('c9aec822-6992-5c93-b34a-33cc0e952b5e');
uuid_version
--------------
5
(1 row)
The function uuid_variant(uuid)
returns the UUID variant as specified in
RFC 4122 with the following values:
0
= NCS backward compatibility1
= RFC 41222
= Microsoft Corporation backwards compatibility3
= Reserved for future definition
Example for a rather ancient CLSID:
SELECT uuid_variant('{000C1090-0000-0000-C000-000000000046}');
uuid_variant
--------------
2
(1 row)
The function uuid_v1_timestamp(uuid)
extracts the timestamp of a version 1
UUID that was generated according to RFC 4122 into an instance of the
PostgreSQL type timestamp with time zone
, e.g.:
SET timezone TO 'Asia/Tokyo';
SELECT uuid_v1_timestamp('b647e96b-862d-11e9-ae2b-db6f0f573554');
uuid_v1_timestamp
-------------------------------
2019-06-04 03:30:50.132721+09
(1 row)
The function ensures that only a UUID which actually contains a readable
timestamp value is actually parsed. In case a value other than an RFC 4122
version 1 UUID is provided, the function will simply return NULL
, e.g. for
a version 4 (random) UUID:
SELECT uuid_v1_timestamp('22859369-3a4f-49ef-8264-1aaf0a953299') IS NULL AS is_null;
is_null
---------
t
(1 row)
The function uuid_v1_node(uuid)
returns the node of a version 1 UUID,
e.g.:
SELECT uuid_v1_node('b647e96b-862d-11e9-ae2b-db6f0f573554');
uuid_v1_node
--------------
db6f0f573554
(1 row)
The function uuid_generate_v1_at(timestamp with time zone)
uses the provided
timestamp value to generate an RFC 4122 version 1 UUID of PostgreSQL
standard data type uuid
:
SELECT uuid_generate_v1_at('2019-06-11 10:02:19.391640Z');
uuid_generate_v1_at
--------------------------------------
ffc44a0e-8c2f-11e9-8000-000000000000
(1 row)
In case of providing a NULL
argument, the function is not invoked and NULL
is returned directly by PostgreSQL.
Straight forward but please ensure that you have the necessary PostgreSQL development headers in-place as well as PGXS (which should be made available with installing the development package).
make
Some basic tests are included by making use of pg_regress
which can be run with:
make installcheck
You might need to create a role with super-user privileges with the same name as your local user or you re-use an existing one, e.g.:
sudo -u postgres make installcheck
If your default PostgreSQL installation doesn't listen on standard port 5432,
you can adapt it by specifying REGRESS_PORT
variable, e.g.:
sudo -u postgres make REGRESS_PORT=5433 installcheck
This also requires PGXS as it figures out where to find the installation:
sudo make install
If you want to install it into a non-default PostgreSQL installation, just
specify the path to the respective pg_config
binary, e.g.:
sudo make PG_CONFIG=/usr/lib/postgresql/10/bin/pg_config install