Thread: Verifying data type

Hello guys,

I have a function like this


CREATE OR REPLACE FUNCTION "public"."f_tipo_campo" (varchar) RETURNS 
numeric AS
'declare p_valor ALIAS FOR $1; v_resultado varchar;
begin  v_resultado := 1;
<>   return v_resultado;
<>end;
<><>' LANGUAGE 'plpgsql'

<>

<>
I need to verify if the data in p_valor is just number or it is a string.
What is the best way to do this?

Here is the original function in Oracle PL/SQL

create or replace function f_tipo_campo (p_valor varchar) return number is
  v_resultado number;
  v_number    number;

begin
  begin
     select p_valor
       into v_number from dual;
     v_resultado := 0;
  exception
     when others then
        v_resultado := 1;
  end;
  return v_resultado;

end;

/

show err

--
Elieser Leão
Sorry for my bad english...

Elieser Leão <elieser@nho.com.br> writes:
> I need to verify if the data in p_valor is just number or it is a string.
> What is the best way to do this?

In PG 8.0 you can just do it exactly the way your Oracle original does,
viz try to cram it into a numeric variable and catch the exception if
any.

In earlier versions, my thoughts would run to some kind of string
matching test using a regular expression.

The regexp method is probably significantly faster though, so maybe you
want to do it anyway, especially if you don't need the full generality
of possible floating-point formats.  You might get away with something
as simple as "p_valor ~ '^[0-9]+$'" if you only care about unsigned
integer inputs.
        regards, tom lane

Thanks, but the number may be a float, like '1.23,00', '12.323,00', '12,34'.... :(<br /> The regex works fine if it
justan integer...<br /><br /> Have some regex to "compare"?<br /><br /> Tom Lane wrote:<br /><blockquote
cite="mid18604.1092699055@sss.pgh.pa.us"type="cite"><pre wrap="">Elieser Leão <a class="moz-txt-link-rfc2396E"
href="mailto:elieser@nho.com.br"><elieser@nho.com.br></a>writes: </pre><blockquote type="cite"><pre wrap="">I
needto verify if the data in p_valor is just number or it is a string.
 
What is the best way to do this?   </pre></blockquote><pre wrap="">
In PG 8.0 you can just do it exactly the way your Oracle original does,
viz try to cram it into a numeric variable and catch the exception if
any.

In earlier versions, my thoughts would run to some kind of string
matching test using a regular expression.

The regexp method is probably significantly faster though, so maybe you
want to do it anyway, especially if you don't need the full generality
of possible floating-point formats.  You might get away with something
as simple as "p_valor ~ '^[0-9]+$'" if you only care about unsigned
integer inputs.
        regards, tom lane
 </pre></blockquote><br />