Re: help with conversion functions

On Mon, 26 Jul 2004, Pradeepkumar, Pyatalo (IE10) wrote:

> I am working on PostgreSql version 7.4.1 and I am referring PostgreSql 7.4.2
> Documentation.
> But TO_NUMERIC(text,text) has 2 parameters....but i want to pass only one
> parameter....that is the problem.
> In the documentation they not explained it clearly....need help on that.

In general, if you can assume that the values are correctly formatted as
the target type, you can probably just use an explicit cast to your
desired type. Something like to_numeric is useful if the format isn't
precisely what the cast expects. What do you want it to do when you
provide 'XYZ' or 'X1X' for a numeric field?

> On Sun, 25 Jul 2004, Pradeepkumar, Pyatalo (IE10) wrote:
>
> > I have written a function to update values in a table. The table structure
> > is as follows:
> > CREATE TABLE PointAttributes(AttributeId BIGINT NOT NULL,
> > PointId BIGINT NOT NULL,
> > StrValue VARCHAR(255),
> > NumValue FLOAT);
> >
> > Based on the attribute datatype, i have to fill up either StrValue(if
> > datatype is character) or NumValue(if the datatype is numeric).
> >
> > My function just updates the above table by providing values. The function
> > is as follows:
> >
> > CREATE OR REPLACE FUNCTION PP_UpdatePointParameter(varchar,varchar,text);
> >
> > the first 2 parameters specify the attributename and pointname. I am
> passing
> > the value as text in the 3rd parameter.
> > Now if the datatype is character, i have to set the StrValue field of the
> > table with text value else if the datatype of the attribute is numeric
> then
> > i have to convert the text to the suitable numeric type and assign it to
> the
> > NumValue.
> >
> > I have problems in doing that.....i have used TO_NUMERIC(text) as
> specified
> > in the POSTGRESQL DOCUMENTATION. When i execute the function, it says
> > function to_numeric("unknown") does not exist. You may need to add
> explicit
> > type casts.
>
> Where did you find that in the docs and which version docs was that?
>
> I'd have suggested to_number(text, text), but it also depends what you
> want to do if someone passes something like '1X1' in for a numeric value.
>
> > And also i am not sure what the return type of my function should. I have
> to
> > just update the table.
>
> In 7.4, you could make it void I think. Otherwise, I'd suggest an
> integer.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>