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




---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: Verifying data type

=?ISO-8859-1?Q?Elieser_Le=E3o?= 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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Re: Verifying data type

This is a multi-part message in MIME format.
--------------020907070702010305060405
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Thanks, but the number may be a float, like '1.23,00', '12.323,00',
'12,34'.... :(
The regex works fine if it just an integer...

Have some regex to "compare"?

Tom Lane wrote:

>=?ISO-8859-1?Q?Elieser_Le=E3o?= 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
>
>
>


--------------020907070702010305060405
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit










Thanks, but the number may be a float, like '1.23,00', '12.323,00',
'12,34'.... :(

The regex works fine if it just an integer...



Have some regex to "compare"?



Tom Lane wrote:

=?ISO-8859-1?Q?Elieser_Le=E3o?= 
 class="moz-txt-link-rfc2396E" href="mailto:elieser@nho.com.br"><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