I was needing something similar last week, not to throw an error,
but to catch an error when 99% of my column's data is real, but some
is not (e.g. '1.2-1.4' or '>32.7'). I wanted to do it in pure
SQL, but settled on this. Is there a way to do this in pure
SQL (so it will be faster)?
Declare x real;
Begin
x = cast($1 as real);
Return x;
Exception
When OTHERS Then
Return NULL;
End;
Thanks,
TJ
On a side note, do you really want to punt to 0 when an invalid value
comes it? That sounds like something MySQL would do... ISTM you should
throw an error.
Also, you could have written that as a pure SQL function, which would
have been faster (assuming you could use something other than C for
this).
create function uint_in(val cstring) returns uint2 as
$$
declare thisval int4;
begin
thisval=val::int4
if thisval between 0 and 65535 then
return (thisval-32768)::int2;
else
return 0;
end if;
end
$$ language 'plpgsql';
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
