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)