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 [EMAIL PROTECTED] Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
