Determining how to convert a value
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
No equivalent to Isnumeric in pgsql that I am aware of. You could create another function to do this. See http://www.webservertalk.com/archive143-2004-5-206410.html
Using Perl instead of pgsql might be easier.
Mike
Show quoted text
On Sun, Jul 11, 2004 at 04:35:53PM -0400, Robert Fitzpatrick wrote:
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