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';
>
>