Ron St-Pierre wrote:
I am trying to use a sequence value in a function but I keep getting an error message:
WARNING: Error occurred while executing PL/pgSQL function correctaddress
WARNING: line 8 at SQL statement
ERROR: column "addressid" is of type integer but expression is of type character varying
You will need to rewrite or cast the expression
And the function looks like:
CREATE FUNCTION correctAddress(INT) RETURNS INT AS ' DECLARE
user_id ALIAS FOR $1;
old_addr INT; new_addr INT; BEGIN
PERFORM nextval(''public.address_addressid_seq'');
If you've set up addressID as a SERIAL then this nextval() isn't necessary.
INSERT INTO address (SELECT strProvince, strAddress FROM address WHERE addressID = (SELECT addressID FROM companies WHERE companyID = (SELECT companyID FROM users WHERE userID=user_id)));
I'm using the force here, but the problem might be here instead. What are the columns on the address table, and if addressID is the first one is strProvince a varchar?
WOW! Amazing use of the force, strProvince is a text field :-) You're correct, the first column is an in and strProvince is text. Thanks for pointing out the obvious, my 'real' function has about 15 more fields and I was too busy looking at other things to notice.
Thanks Richard.
UPDATE users SET adressID = CAST(currval(''public.tbladdress_addressid_seq'') AS INTEGER) WHERE userID=user_id; -- ---> ^ ^ ^ ^ ^ ^ = ?
Ron
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
