Thread: help with conversion functions
Hi,
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.
And also i am not sure what the return type of my function should. I have to
just update the table.
I need help on this.
> With Best Regards
> Pradeep Kumar P J
>
>
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.
Hi,
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.
-----Original Message-----
From: Stephan Szabo [mailto:sszabo@megazone.bigpanda.com]
Sent: Monday, July 26, 2004 7:15 PM
To: Pradeepkumar, Pyatalo (IE10)
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] 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.
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
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@postgresql.org
>