Re: Function - sequence - cast

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