Creating a unique identifier... - Mailing list pgsql-general

I've created this elaborate scheme to create small unique identifiers for
external transactions. It is built on a compact representation of the
current_timestamp(6) + 2 peices of data (varchar(50)) + a sequence number. I'd
like the option of restarting the sequence at some later date, but I don't want
to replicate any IDs.

To shorten the identifier I thought it might be a good idea to run an MD5
digest using the functionality from contrib/pgcrypto on it. ("thought" and
"might" are the key words there...)

I just need another (thousand?) set of eyes to see if a) I've missed anything,
b) There's a better way... :)

-- Sequence: universal_sq
CREATE SEQUENCE universal_sq INCREMENT 1 MINVALUE 1 MAXVALUE
9223372036854775807 CACHE 1;

-- Creates an text/interger representation of the current time
-- with microseconds.
CREATE FUNCTION "dtid"() RETURNS "text" AS '
  DECLARE
    tstz timestamptz;
    dtid text;
  BEGIN
    tstz := current_timestamp;
    dtid := (((tstz::ABSTIME::INT4)/100 * 100000000::INT8) +
date_part(\'microseconds\',tstz))::INT8::TEXT;
    RETURN dtid;
  END;' LANGUAGE 'plpgsql';

SELECT encode(digest(dtid() || 'value1' || 'value2' ||
nextval('universal_sq')::text, 'md5'),'hex');



__________________________________________________
Do you Yahoo!?
HotJobs - Search new jobs daily now
http://hotjobs.yahoo.com/