1 create or replace function uuid_bytespp(howmany integer) returns bytea as $$
5 bytes = rpad('', howmany);
7 for i in 0 .. howmany-1 loop
8 bytes := set_byte(bytes, i, (random()*256)::integer);
13 $$ language 'plpgsql';
15 create or replace function uuid_v1pp() returns uuid as $$
28 select extract(epoch from now() - '1582-10-15 00:00:00 UTC'::timestamp with time zone)::bigint * 10000000 +
29 extract(microseconds from now() - '1582-10-15 00:00:00 UTC'::timestamp with time zone)::integer % 1000000 * 10
33 tslow := decode(lpad(to_hex(tsi & x'ffffffff'::bigint), 8, '0'), 'hex');
34 tsmid := decode(lpad(to_hex((tsi>>32) & x'ffff'::bigint), 4, '0'), 'hex');
35 tshigh := decode(lpad(to_hex((tsi>>48) & x'ffff'::bigint), 4, '0'), 'hex');
37 -- we don't store any state, so the clock sequence is random,
38 -- and we don't have any way to get the macaddr, so that's
39 -- random too. though we set the multicast bit (the real one,
40 -- not the one in the rfc)
41 bytes := tslow || tsmid || tshigh || uuid_bytespp(8);
43 byte = get_byte(bytes, 6);
44 byte = (byte & 15) | 16;
45 bytes = set_byte(bytes, 6, byte);
47 byte = get_byte(bytes, 8);
48 byte = (byte & 63) | 128;
49 bytes = set_byte(bytes, 8, byte);
51 byte = get_byte(bytes, 10);
52 byte = (byte & 127) | 128;
53 bytes = set_byte(bytes, 10, byte);
55 str = encode(bytes, 'hex');
59 $$ language 'plpgsql';
61 create or replace function uuid_v4pp() returns uuid as $$
68 bytes = uuid_bytespp(16);
70 byte = get_byte(bytes, 6);
71 byte = (byte & 15) | 64;
72 bytes = set_byte(bytes, 6, byte);
74 byte = get_byte(bytes, 8);
75 byte = (byte & 63) | 128;
76 bytes = set_byte(bytes, 8, byte);
78 str = encode(bytes, 'hex');
82 $$ language 'plpgsql';
84 create or replace function uuid_v3pp(ns uuid, content bytea) returns uuid as $$
92 nsbytes = decode(regexp_replace(ns::text, '-', '', 'g'), 'hex');
93 bytes = decode(md5(nsbytes || content), 'hex');
95 byte = get_byte(bytes, 6);
96 byte = (byte & 15) | 48;
97 bytes = set_byte(bytes, 6, byte);
99 byte = get_byte(bytes, 8);
100 byte = (byte & 63) | 128;
101 bytes = set_byte(bytes, 8, byte);
103 str = encode(bytes, 'hex');
107 $$ language 'plpgsql';
109 create or replace function uuid_v5pp(ns uuid, content bytea) returns uuid as $$
117 nsbytes = decode(regexp_replace(ns::text, '-', '', 'g'), 'hex');
118 bytes = substring(decode(sha1(nsbytes || content), 'hex') from 1 for 16);
120 byte = get_byte(bytes, 6);
121 byte = (byte & 15) | 80;
122 bytes = set_byte(bytes, 6, byte);
124 byte = get_byte(bytes, 8);
125 byte = (byte & 63) | 128;
126 bytes = set_byte(bytes, 8, byte);
128 str = encode(bytes, 'hex');
132 $$ language 'plpgsql';