NoSQL-like Unique Identifiers in SQL

generate_object_id.sql

-- This file is released under the MIT License (MIT)
--
-- Copyright (c) 2013 Jamar Parris
-- Copyright (c) 2016 Andrew T. Finnell <[email protected]>
--
-- Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated 
-- documentation files (the "Software"), to deal in the Software without restriction, including without limitation
-- the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, 
-- and to permit persons to whom the Software is furnished to do so, subject to the following conditions:
--
-- The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.
--
-- THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
-- WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS 
-- OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR 
-- OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

-- Turn on the extension for better random byte generation
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Use a sequence to ensure that at least 10 simultaneous calls
-- to this function could never result in the same id. This uses
-- gen_random_bytes() but that does not gurantee the return bytes
-- is unique. It's possible EPOCH returns the same timestamp and 
-- the random bytes are the same. This would cause a conflict
-- thus breaking the entire reason to use this function.
CREATE SEQUENCE epoch_seq INCREMENT BY 1 MAXVALUE 9 CYCLE;

CREATE OR REPLACE FUNCTION generate_object_id() RETURNS varchar AS $$
    DECLARE
        time_component bigint;
        epoch_seq int;
        machine_id text := encode(gen_random_bytes(3), 'hex');
        process_id bigint;
        seq_id text := encode(gen_random_bytes(3), 'hex');
        result varchar:= '';
    BEGIN
        SELECT FLOOR(EXTRACT(EPOCH FROM clock_timestamp())) INTO time_component;
        SELECT nextval('epoch_seq') INTO epoch_seq;
        SELECT pg_backend_pid() INTO process_id;

        result := result || lpad(to_hex(time_component), 8, '0');
        result := result || machine_id;
        result := result || lpad(to_hex(process_id), 4, '0');
        result := result || seq_id;
        result := result || epoch_seq;
        RETURN result;
    END;
$$ LANGUAGE PLPGSQL;

generate_uuid_id.postgres.sql

-- This file is released under the MIT License (MIT)
--
-- Copyright © 2918 Andrew T. Finnell <[email protected]>
-- All rights reserved worldwide.
--
-- Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated 
-- documentation files (the "Software"), to deal in the Software without restriction, including without limitation
-- the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, 
-- and to permit persons to whom the Software is furnished to do so, subject to the following conditions:
--
-- The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.
--
-- THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
-- WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS 
-- OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR 
-- OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
--

-- Turn on the UUID Plugin in order to generate UUID's directly instead of
-- the database doing it when a record is inserted.
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE OR REPLACE FUNCTION generate_uuid_id() RETURNS varchar AS $$
    DECLARE
       result varchar:= '';
    BEGIN
        SELECT uuid_generate_v4() INTO result;
        return result;
    END
$$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION generate_uuid_id_noext() RETURNS varchar AS $$
    DECLARE
        result varchar:= '';
    BEGIN
      SELECT overlay(
          overlay(
              md5(random()::text || ':' || clock_timestamp()::text) placing '4' from 13) 
                placing floor(random()*(11-8+1) + 8)::text from 17)::uuid INTO result;
      return result;
    END;
$$ LANGUAGE PLPGSQL;

@andrew-finnell, doesn't this create a string that is 25 characters long, instead of the "standard" length of 24?

@grawlinson I believe you are correct. I'll look at putting this into a real repo and adding unit tests for it. Thanks!