Using pl/pgSQL on 7.4.3, I have a varchar column called unit name in a table that can be numeric, of course. Most of the time, end users will put A, B, C, D, etc. or 101, 102, 103, etc. I am trying to write a function to determine the next available number after the first is given. For instance, so far I have a function that will determine 102 is next if 101 was used first by using the int2() function to convert it first. But, of course, I get an error 'ERROR: invalid input syntax for integer: "A"' if they use A first because A is not numeric. How can I try the value with int2() first and then pass it to the appropriate function instead of int2() like below or determine the type of value first?
CREATE OR REPLACE FUNCTION "public"."next_unit_name" (integer) RETURNS varchar AS'
DECLARE
similargroup alias for $1;
unit record;
BEGIN
SELECT INTO unit public.tblhudunits.unit_name FROM public.tblhudunits WHERE (public.tblhudunits.similar_group_id = similargroup) ORDER BY public.tblhudunits.unit_name DESC;
IF FOUND AND unit.unit_name <> '''' THEN
return int2(unit.unit_name) + 1;
ELSE
return ''101'';
END IF;
END;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
--
Robert