Re: help with conversion functions

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.