http://www.slony.info/bugzilla/show_bug.cgi?id=335

--- Comment #1 from Christopher Browne <cbbrowne at ca.afilias.info> 2014-03-19 13:54:17 PDT ---
Here is a function that generates well-formed UUIDs of Type 1 form:

create or replace function public.make_retroactive_uuid (p_trid integer, p_date
timestamptz, p_suffix text)
returns uuid as
$$
declare
   c_uuid uuid;
   c_epoch bigint;
   c_tlow character(8);
   c_tmid character(4);
   c_version character(1);
   c_thi character(4);
   c_seq character(4);
   c_node character(12);
begin
   if p_date is null then
     c_epoch := 10000000*(extract(epoch from
'1970-01-01'::timestamptz)+12219292800::bigint)::bigint;
   else
     c_epoch := (10000000*(extract(epoch from
p_date)+12219292800::bigint))::bigint;
   end if;
   c_tlow := lpad(to_hex(mod(c_epoch, 4294967296::bigint) 
                         # mod(p_trid/8192, 256)
                    ), 8, '0');
   c_tmid := lpad(to_hex(mod(c_epoch /4294967296::bigint, 65536)),4,'0');
   c_version := '1';
   c_thi := lpad(to_hex(c_epoch /281474976710656::bigint),3,'0');
   c_seq := lpad(to_hex((B'10000000' | mod(p_trid/256,
32)::bit(8))::integer),2,'0')
              || lpad(to_hex(mod(p_trid, 256)), 2, '0');
   c_node := p_suffix;
   c_uuid := (c_tlow || '-' || c_tmid || '-' || c_version || c_thi || '-' ||
c_seq || '-' || c_node)::uuid;
   return c_uuid;
end
$$ language plpgsql;

Usage example:

select public.make_retroactive_uuid(1, now(), 'f6b3a3220461');
        make_retroactive_uuid         
--------------------------------------
 b39dde40-afa7-11e3-8001-f6b3a3220461
(1 row)


That can then be decoded using OSSP UUID code:

> uuid -d b39dde40-afa7-11e3-8001-f6b3a3220461
encode: STR:     b39dde40-afa7-11e3-8001-f6b3a3220461
        SIV:     238751509672186172578688542368744997985
decode: variant: DCE 1.1, ISO/IEC 11578:1996
        version: 1 (time and node based)
        content: time:  2014-03-19 20:47:35.911379.2 UTC
                 clock: 1 (usually random)
                 node:  f6:b3:a3:22:04:61 (local unicast)

Note that type 1 is the usual sort generated by default.

-- 
Configure bugmail: http://www.slony.info/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are on the CC list for the bug.
You are the assignee for the bug.