Thread: newid() in postgres

Hello NG,

Is there an equivalent in postgres for the newid() function like in
sqlserver? I need to generate a unique identifier in my select
statement:

SELECT "X", newid(), "Y" FROM "MyTable"

X
newid()
Y
------------------------------------------------------------------------------------------------------------
1          139A7882-CF95-7C44-AC64-DF4D18614CAD              test
2          D4CD37FE-4BD6-954B-B188-0D5BE0BDCF0E            test2
...


I've searched in the groups already, but couldn't find any helpful
information - only to use a sequence, which returns just a number and
not a unique identifier.

Thanks in advance

Marcel

marcel.beutner wrote:
> I've searched in the groups already, but couldn't find any helpful
> information - only to use a sequence, which returns just a number and
> not a unique identifier.

Which properties do your unique identifiers posses that are not
satisfied by a number returned by a sequence?

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Marcel,

A sequence represents a unique identifier. You can call the function
'nextval' to get the next unique value in the sequence.  See related
functions here:

http://www.postgresql.org/docs/8.2/interactive/functions-sequence.html

In this code, I get the next sequence, insert it into a table, and
then return the value to the calling function:

DECLARE
     nextseq integer;

BEGIN
    nextseq := nextval('entry_id_seq');

    INSERT INTO my_table (
        entry_id,
        entry_text,
    ) VALUES (
        nextseq,
        p_entry_text,  -- input param
    );

On 4/4/07, Peter Eisentraut <peter_e@gmx.net> wrote:
> marcel.beutner wrote:
> > I've searched in the groups already, but couldn't find any helpful
> > information - only to use a sequence, which returns just a number and
> > not a unique identifier.
>
> Which properties do your unique identifiers posses that are not
> satisfied by a number returned by a sequence?
>
> --
> Peter Eisentraut
> http://developer.postgresql.org/~petere/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq
>

Hello,

Thanks a lot for your answers! But I don't need a sequence which only
will be incremented. I need a _real_ GUID just as the newid()
function. Is there no way to generate such a GUID?

I need a real GUID because I use them further in my host app. And my
host app relies on it.


Thanks for your answers.

Marcel

You'll need to create a custom function in Postgres to support this,
which is fairly easy.  It's been done before- do a search on Google:

http://www.hclausen.net/psql.php

On 5 Apr 2007 01:27:15 -0700, marcel.beutner <m.beutner@googlemail.com> wrote:
> Hello,
>
> Thanks a lot for your answers! But I don't need a sequence which only
> will be incremented. I need a _real_ GUID just as the newid()
> function. Is there no way to generate such a GUID?
>
> I need a real GUID because I use them further in my host app. And my
> host app relies on it.
>
>
> Thanks for your answers.
>
> Marcel
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

Thanks,
I'll try to implement it.

Marcel

Here's a PL/pgsql implementation I wrote.....I'm sure critics will be
able to improve upon it:

CREATE or REPLACE FUNCTION "common"."newid"()
RETURNS "pg_catalog"."varchar" AS
$BODY$
DECLARE
  v_seed_value varchar(32);
BEGIN
select
md5(
inet_client_addr()::varchar ||
timeofday() ||
inet_server_addr()::varchar ||
to_hex(inet_client_port())
)
into v_seed_value;

return (substr(v_seed_value,1,8) || '-' ||
        substr(v_seed_value,9,4) || '-' ||
        substr(v_seed_value,13,4) || '-' ||
        substr(v_seed_value,17,4) || '-' ||
        substr(v_seed_value,21,12));
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;