Determining how to convert a value

From: "Robert Fitzpatrick" <robert(at)webtent(dot)com>
To: "PostgreSQL" <pgsql-general(at)postgresql(dot)org>
Subject: Determining how to convert a value
Date: 2004-07-11 20:35:53
Message-ID: 000801c46787$500bc110$018ffea9@webtent.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jerry LeVan 2004-07-11 21:08:25 Is a digest avail for postgresql-general?
Previous Message Ralph Counts 2004-07-11 16:18:16