Determining how to convert a value

[Robert Fitzpatrick]

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 firstby using the int2() function to convert it first. But, of course, I get anerror '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 thenpass 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.tblhudun its.unit_name FROM public.tblhudun its WHERE (public.tblhudu nits.similar_gr oup_id = similargroup) ORDER BY public.tblhudun its.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

[Mike G]

No equivalent to Isnumeric in pgsql that I am aware of. You could create another function to do this. See http://www.webservertalk.com/archive...-5-206410.html

Using Perl instead of pgsql might be easier.

Mike
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.tblhudun its.unit_name FROM public.tblhudun its WHERE (public.tblhudu nits.similar_gr oup_id = similargroup) ORDER BY public.tblhudun its.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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster