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?
UPDATE users SET adressID = CAST(currval(''public.tbladdress_addressid_seq'') AS INTEGER) WHERE userID=user_id; -- ---> ^ ^ ^ ^ ^ ^ = ?
-- Richard Huxton Archonet Ltd
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
