Обсуждение: ...lame use of casting, looking for workaround...

    One of the rocket scientests working on this project has
created a system in which /either/ a description or a build
number is stored in a text field.  He has been writing reports
that use CAST AS('description', int4) to convert the field into
either 0 or a number.  It looks like 7.1beta4 doesn't like this
silly hack, since I get pg_atoi errors when the query hits
anything that doesn't consist entirely of a number (and rightly
so).

    I'm looking for a workaround that will let him do his
"conversion" transparently: some form of atoi that will return
0 if it finds no numbers instead of blowing up.  Anyone got
one?


--
Adam Haberlach            | All your base are belong to us.
adam@newsnipple.com       |
http://www.newsnipple.com |
'88 EX500    '00 >^<      |

Adam Haberlach wrote:
>
>         One of the rocket scientests working on this project has
> created a system in which /either/ a description or a build
> number is stored in a text field.  He has been writing reports

Get that man a job on son of star-wars!

> that use CAST AS('description', int4) to convert the field into
> either 0 or a number.  It looks like 7.1beta4 doesn't like this
> silly hack, since I get pg_atoi errors when the query hits
> anything that doesn't consist entirely of a number (and rightly
> so).

Well, to_number does you no good since it seeks out the first thing that
can be used as a number in a piece of text.

So - either use pattern matching to select-out numbers before using
cast, or write yourself a function that checks whether the text starts
with digits before casting. You can write functions in C or plpgsql for this.

>
>         I'm looking for a workaround that will let him do his
> "conversion" transparently: some form of atoi that will return
> 0 if it finds no numbers instead of blowing up.  Anyone got
> one?

- Richard Huxton

Richard Huxton <dev@archonet.com> writes:

> >         I'm looking for a workaround that will let him do his
> > "conversion" transparently: some form of atoi that will return
> > 0 if it finds no numbers instead of blowing up.  Anyone got
> > one?

The proper solution, of course, is to slap him silly and then make him
rewrite his code.  ;)

-Doug

On Sat, Feb 24, 2001 at 11:16:49AM +0000, Richard Huxton wrote:
> Adam Haberlach wrote:
> >
> >         One of the rocket scientests working on this project has
> > created a system in which /either/ a description or a build
> > number is stored in a text field.  He has been writing reports
>
> Get that man a job on son of star-wars!
>
> > that use CAST AS('description', int4) to convert the field into
> > either 0 or a number.  It looks like 7.1beta4 doesn't like this
> > silly hack, since I get pg_atoi errors when the query hits
> > anything that doesn't consist entirely of a number (and rightly
> > so).
>
> Well, to_number does you no good since it seeks out the first thing that
> can be used as a number in a piece of text.

    I couldn't figure out how to use to_number from the documentation (I'm
beginning to think I should join the "more docs now" chant).  Can someone
tell me how I would find the first number in a text field using to_number?

> So - either use pattern matching to select-out numbers before using
> cast, or write yourself a function that checks whether the text starts
> with digits before casting. You can write functions in C or plpgsql for this.

    I've got a regex that will do that, but that, as far as I know, only
checks for matches, and won't provide the numbers themselves so we can
put them in a BETWEEN clause.

    If all else fails, I'll write the C function.

--
Adam Haberlach            | All your base are belong to us.
adam@newsnipple.com       |
http://www.newsnipple.com |
'88 EX500    '00 >^<      |

From: "Adam Haberlach" <adam@newsnipple.com>

> >
> > Well, to_number does you no good since it seeks out the first thing that
> > can be used as a number in a piece of text.
>
> I couldn't figure out how to use to_number from the documentation (I'm
> beginning to think I should join the "more docs now" chant).  Can someone
> tell me how I would find the first number in a text field using to_number?

It's covered in functions-formatting.htm, basically

richardh=> select to_number('XXX 123 apple','999D99');
 to_number
-----------
       123
(1 row)

Not sure it works the way I think it should, but I'd need to read the docs
more closely.

> > So - either use pattern matching to select-out numbers before using
> > cast, or write yourself a function that checks whether the text starts
> > with digits before casting. You can write functions in C or plpgsql for
this.
>
> I've got a regex that will do that, but that, as far as I know, only
> checks for matches, and won't provide the numbers themselves so we can
> put them in a BETWEEN clause.

Ah, but if the regex has proved this field only contains numbers, a simple
cast should do it, or am I missing something?

>
> If all else fails, I'll write the C function.
>
> --
> Adam Haberlach            | All your base are belong to us.
> adam@newsnipple.com       |
> http://www.newsnipple.com |
> '88 EX500    '00 >^<      |

- Richard Huxton