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