Thread: Determining how to convert a value

Determining how to convert a value

From

"Robert Fitzpatrick"

Date:

11 July 2004, 20:41:30

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

Re: Determining how to convert a value

From

Mike G

Date:

11 July 2004, 23:24:34

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
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
thetime, end users will put A, B, C, D, etc. or 101, 102, 103, etc. I am trying to write a function to determine the
nextavailable number after the first is given. For instance, so far I have a function that will determine 102 is next
if101 was used first by using the int2() function to convert it first. But, of course, I get an error 'ERROR: invalid
inputsyntax for integer: "A"' if they use A first because A is not numeric. How can I try the value with int2() first
andthen 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