Search Postgresql Archives

Re: Function - sequence - cast

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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?


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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux