1

I'm trying to replicate Java UUID.nameUUIDFromBytes method in Postgres.

I've created uuid-ossp extension

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

In Java I call

UUID.nameUUIDFromBytes((NAMESPACE + someString).getBytes())

Both NAMESPACE and someString are of type String. But uuid_generate_v3 accepts (uuid, varchar). Passing uuid_generate_v3(uuid(NAMESPACE), someString) generates different from java UUID method result. Wondering if it is possible to get the same output without rewriting the whole logic explicitly as there is a solution described by Győző Papp in a similar question where the reverse result was desired though. Although it works correctly, it's quite verbose and tightly coupled with Java lib. Would be grateful if anyone could advise some more stable and reliable solution.

Thank you!

alexlys
  • 49
  • 5

1 Answers1

1

This function generates a UUID v3 with no namespace. A v3 is just an MD5 hash with 6 fixed bits. This function uses set_bit() to set each fixed bit.

/**
 * Generate a UUID v3 without namespace.
 *
 * @param p_name a string
 */
create or replace function fn_uuid_v3(p_name varchar) returns uuid as $$
declare
    v_bytes  bytea;
begin

    -- Calculate the MD5 of string
    v_bytes := decode(md5(p_name::bytea), 'hex');

    -- Set version bits (0011)
    v_bytes := set_bit(v_bytes, 55, 0);
    v_bytes := set_bit(v_bytes, 54, 0);
    v_bytes := set_bit(v_bytes, 53, 1);
    v_bytes := set_bit(v_bytes, 52, 1);

    -- Set variant bits (10xx)
    v_bytes := set_bit(v_bytes, 71, 1);
    v_bytes := set_bit(v_bytes, 70, 0);

    return encode(v_bytes, 'hex')::uuid;

end $$ language plpgsql;

You can test it using this query:

select fn_uuid_v3('this is a test');

Output:

54b0c58c-7ce9-32a8-b551-351102ee0938
fabiolimace
  • 972
  • 11
  • 13