Re: Determining how to convert a value

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 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