From: | TJ O'Donnell <tjo(at)acm(dot)org> |
---|---|
To: | jnasby(at)pervasive(dot)com, pgsql-general(at)postgresql(dot)org |
Subject: | Re: new type question |
Date: | 2005-10-17 20:05:17 |
Message-ID: | 435403FD.8040107@acm.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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';
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Ned Lilly | 2005-10-17 20:32:40 | Re: [pgsql-advocacy] Oracle buys Innobase |
Previous Message | Jim C. Nasby | 2005-10-17 19:45:43 | Re: Oracle buys Innobase |