I tried below function as which can be used as default to column. But every time we need to created 2 sequences, 1st one takes care of the first 8 bytes and 2nd takes care of the 2nd part of the UUID. I have not tested index and space utilization. I have to examine this. This might not be completely unique in the nature. but still trying for the best.
CREATE OR REPLACE FUNCTION public.fnu_generate_sequential_uuid(
sequence1 text,
sequence2 text)
RETURNS uuid
LANGUAGE 'plpgsql'
COST 100.0
VOLATILE NOT LEAKPROOF
AS $function$
DECLARE
sequenceUUIDPart1 text;
randomUUIDPart2 text;
counter integer:=0;
significantByte integer:=0;
startIndex integer:=0;
endIndex integer:=0;
BEGIN
-- Get random UUID
randomUUIDPart2 := replace(( uuid_generate_v4 () :: text),'-','');
-- verify first sequence reached max count.
IF to_hex(currval(sequence1)) :: text = '7fffffffffffffff' THEN
startIndex:=0;
endIndex:=7;
-- convert sequence into 32 bit string
sequenceUUIDPart1 = rpad(to_hex(nextval(sequence2))::text, 32, '0');
ELSE
startIndex:=8;
endIndex:=15;
-- convert sequence into 32 bit string
sequenceUUIDPart1 = rpad(to_hex(nextval(sequence1))::text, 32, '0');
END IF;
RAISE NOTICE 'current Guid: %', sequenceUUIDPart1;
-- loop through the 8th byte to 16th byte, till first sequence max .
-- loop through the 0 to 7 the byte till second sequence end.
FOR counter IN startIndex..endIndex LOOP
select get_byte(decode(sequenceUUIDPart1::text,'hex'), counter) into significantByte;
-- fill last 8 bytes with the generated random UUID values.
sequenceUUIDPart1 := encode(set_byte(decode(sequenceUUIDPart1 ::text,'hex') :: bytea ,counter, significantByte ) :: bytea, 'hex') :: text;
RAISE NOTICE 'current Guid: %', sequenceUUIDPart1;
END LOOP;
return sequenceUUIDPart1 :: UUID;
EXCEPTION
WHEN OTHERS
THEN
RAISE EXCEPTION 'An error was encountered in create_engagement_data_get_aud_area_ent_list - % -ERROR- %', sqlstate, sqlerrm;
END
$function$;