Hrm, I thought there was something equivalent to an is_number()
function, which would have made this easy:
CASE WHEN is_number(x) THEN x ELSE NULL END;
But I can't seem to find one. Is there a historic reason such functions
don't exist?
On Mon, Oct 17, 2005 at 01:05:17PM -0700, TJ O'Donnell wrote:
> 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
>
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461