Re: Generating unique values for TEXT columns

Frank,

El 03/01/2005 10:53 AM, Frank D. Engel, Jr. en su mensaje escribio:
> Is there any "convenient" way to generate (on request) a unique value
> for a TEXT column?  I have a situation in which I want users of my
> front-end program to be able to manually enter values for this column,
> but if they leave it blank (in the front-end), to have the database
> automatically fill in a unique value.  I would like to restrict the
> unique values to (for example) digits and uppercase letters (this is
> flexible, but the uniqueness of the values should be visually
> discernible, and all characters should be printable).
>
> I know how to do this with a numeric column (I can just SELECT MAX on
> the column and add one, for example), but how can this be done with a
> TEXT column?

I have plpgsql function to generate random character ids:

CREATE OR REPLACE FUNCTION "public"."basex" (integer, varchar) RETURNS
varchar AS'
DECLARE
    lnval ALIAS for $1;
    tcdom alias for $2;
    lndomsiz integer;
    lndig integer;
    lcret varchar;
    lnval2 integer ;
    lnpos integer;
    lcdig varchar;
BEGIN
    lndomsiz := char_length(tcdom) ;
    lnVal2 := lnVal;
    lcret :='''';
    while lnVal2 <> 0 loop
        lndig := lnVal2 % lnDomSiz ;
        lnval2 := trunc ( lnVal2/lnDomSiz ) ;
        lnpos := lnDig+1 ;
        lcdig := substr(tcdom,lnpos,1);
        lcret := lcdig || lcret ;
    end loop;
    return lcret;
END;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

Usually I call it this way:

select lpad(basex(nextval('sqrefno')::int,'12456789CFHRWY'),6,'0');


--
Sinceramente,
Josué Maldonado.

"Que se me den seis líneas escritas de puño y letra del hombre más
honrado del mundo, y hallaré en ellas motivos para hacerle ahorcar."
--cardenal Richelieu (Cardenal y político francés. 1.585 - 1.642)