 | |
help with conversion functions
comp.databases.postgresql.novice comp.databases.postgresql.novice
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
help with conversion functions -
07-26-2004
, 01:46 AM
|
With Best Regards Pradeep Kumar P J |
TIP 4: Don't 'kill -9' the postmaster
#2
| |||
| |||
|
|
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. |
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.
Quote:
|
And also i am not sure what the return type of my function should. I have to just update the table. |
integer.
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org
#3
| |||
| |||
|
|
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. |
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.
Quote:
|
And also i am not sure what the return type of my function should. I have to just update the table. |
integer.
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
#4
| |||
| |||
|
|
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. |
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?
Quote:
|
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 |
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.