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

elieser [ Mo, 16 August 2004 23:16 ] [ ID #453279 ]

Re: Verifying data type

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

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

tgl [ Di, 17 August 2004 01:30 ] [ ID #453280 ]

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?= <elieser [at] 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
>
>
>

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

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
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: 
<blockquote cite="mid18604.1092699055 [at] sss.pgh.pa.us" type="cite">
<pre wrap="">=?ISO-8859-1?Q?Elieser_Le=E3o?= <a
class="moz-txt-link-rfc2396E" href="mailto:elieser [at] nho.com.br"><elieser [at] nho.com.br></a> writes:
</pre>
<blockquote type="cite">
<pre wrap="">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?
</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>
 
</body>
</html>

--------------020907070702010305060405--

elieser [ Di, 17 August 2004 15:48 ] [ ID #453291 ]