pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedVerifying data type
3+ messages / 2 participants
[nested] [flat]
* Verifying data type
@ 2004-08-16 21:16 Elieser Leão <elieser@nho.com.br>
2004-08-16 23:30 ` Re: Verifying data type Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: Elieser Leão @ 2004-08-16 21:16 UTC (permalink / raw)
To: pgsql-sql
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...
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Verifying data type
2004-08-16 21:16 Verifying data type Elieser Leão <elieser@nho.com.br>
@ 2004-08-16 23:30 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 2004-08-16 23:30 UTC (permalink / raw)
To: Elieser Leão <elieser@nho.com.br>; +Cc: pgsql-sql
=?ISO-8859-1?Q?Elieser_Le=E3o?= <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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Verifying data type
@ 2004-08-17 13:48 Elieser Leão <elieser@nho.com.br>
0 siblings, 0 replies; 3+ messages in thread
From: Elieser Leão @ 2004-08-17 13:48 UTC (permalink / raw)
To: pgsql-sql
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@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
>
>
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-08-17 13:48 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-08-16 21:16 Verifying data type Elieser Leão <elieser@nho.com.br>
2004-08-16 23:30 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-08-17 13:48 Re: Verifying data type Elieser Leão <elieser@nho.com.br>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox