bjeanes/AAA_uuid_generate_v6mc.sql

## README.md

      
    Raw
  

              README.md
            
          
    Postgres UUIDv6 (non-standard) using randomised MAC address.
I.e. lexicographically sortable (and indexable) UUIDs.
Version bit set to 6.
Ported from https://github.com/kurttheviking/uuid-with-v6-js. which is an interpretation of

http://gh.peabody.io/uuidv6/ /
https://tools.ietf.org/html/draft-peabody-dispatch-new-uuid-format

Licence

Licenced under ISC.
Copyright 2020 Bo Jeanes (@bjeanes)

Permission to use, copy, modify, and/or distribute this software for any purpose with or without fee is hereby granted, provided that the above copyright notice and this permission notice appear in all copies.

THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE.


## AAA_uuid_generate_v6mc.sql
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- My port of https://github.com/kurttheviking/uuid-with-v6-js
-- A hacky translation of:
--   - http://gh.peabody.io/uuidv6/
--   - https://tools.ietf.org/html/draft-peabody-dispatch-new-uuid-format
CREATE OR REPLACE FUNCTION uuid_v1_to_v6(v1 uuid)
RETURNS uuid AS $$
DECLARE
    v6 text;
BEGIN
    SELECT substring(v1::text from 16 for 3) ||
           substring(v1::text from 10 for 4) ||
           substring(v1::text from 1 for 5)  ||
           '6' || substring(v1::text from 6 for 3) ||
           substring(v1::text from 20)

           INTO v6;

    RETURN v6::uuid;
END;
$$
LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION uuid_generate_v6mc()
RETURNS uuid AS $$
BEGIN
    RETURN uuid_v1_to_v6(uuid_generate_v1mc());
END; $$
LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION uuid_generate_v6()
RETURNS uuid AS $$
BEGIN
    RETURN uuid_v1_to_v6(uuid_generate_v1());
END; $$
LANGUAGE PLPGSQL;

## example.md

      
    Raw
  

              example.md
            
          
    # select uuid_generate_v6mc() from generate_series(1,20);
          uuid_generate_v6mc
--------------------------------------
 1eaa7858-863b-689c-9fb4-87df3813ba36
 1eaa7858-863b-69c8-9fb4-bfc6e6e3a1f2
 1eaa7858-863b-6a54-9fb4-9f139bb05d7b
 1eaa7858-863b-6ad6-9fb4-87f6e9bb691f
 1eaa7858-863b-6b4e-9fb4-f72b808b9a8f
 1eaa7858-863b-6bd0-9fb4-077dc69a4bf8
 1eaa7858-863b-6c48-9fb4-c70ecaccee2f
 1eaa7858-863b-6cca-9fb4-fbf95e0edadf
 1eaa7858-863b-6d42-9fb4-cbd804a00457
 1eaa7858-863b-6e32-9fb4-9baa9f476601
 1eaa7858-863b-6ee6-9fb4-e3f33d0d9577
 1eaa7858-863b-6f72-9fb4-2779ce4270e8
 1eaa7858-863b-6ff4-9fb4-ef2cea331f95
 1eaa7858-863c-606c-9fb4-fbcdb18ae615
 1eaa7858-863c-60ee-9fb4-33c7b54c58b6
 1eaa7858-863c-6166-9fb4-17d009923e41
 1eaa7858-863c-61e8-9fb4-27714feb33fb
 1eaa7858-863c-6260-9fb4-738ce6f88eb2
 1eaa7858-863c-62e2-9fb4-9b7794839b18
 1eaa7858-863c-635a-9fb4-fbf96ab41c09
(20 rows)
	CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

	-- My port of https://github.com/kurttheviking/uuid-with-v6-js
	-- A hacky translation of:
	-- - http://gh.peabody.io/uuidv6/
	-- - https://tools.ietf.org/html/draft-peabody-dispatch-new-uuid-format
	CREATE OR REPLACE FUNCTION uuid_v1_to_v6(v1 uuid)
	RETURNS uuid AS $$
	DECLARE
	v6 text;
	BEGIN
	SELECT substring(v1::text from 16 for 3) \|\|
	substring(v1::text from 10 for 4) \|\|
	substring(v1::text from 1 for 5) \|\|
	'6' \|\| substring(v1::text from 6 for 3) \|\|
	substring(v1::text from 20)

	INTO v6;

	RETURN v6::uuid;
	END;
	$$
	LANGUAGE PLPGSQL;

	CREATE OR REPLACE FUNCTION uuid_generate_v6mc()
	RETURNS uuid AS $$
	BEGIN
	RETURN uuid_v1_to_v6(uuid_generate_v1mc());
	END; $$
	LANGUAGE PLPGSQL;

	CREATE OR REPLACE FUNCTION uuid_generate_v6()
	RETURNS uuid AS $$
	BEGIN
	RETURN uuid_v1_to_v6(uuid_generate_v1());
	END; $$
	LANGUAGE PLPGSQL;