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)
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
