Skip site navigation (1) Skip section navigation (2)

Re: help with conversion functions

From: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
To: "Pradeepkumar, Pyatalo (IE10)" <Pradeepkumar(dot)Pyatalo(at)honeywell(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: help with conversion functions
Date: 2004-07-26 13:45:07
Message-ID: 20040726064021.E63435@megazone.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-novice
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.

In response to

pgsql-novice by date

Next:From: Marc G. FournierDate: 2004-07-26 16:29:02
Subject: Re: Request for information about 3rd party support for
Previous:From: ChrisDate: 2004-07-26 12:16:18
Subject: hexdecimal constant in postgresql

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group