Re: Function - sequence - cast

Richard Huxton wrote:

> 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.

>
>> UPDATE users SET adressID =
>> CAST(currval(''public.tbladdress_addressid_seq'') AS INTEGER) WHERE
>> userID=user_id;
>> -- ---> ^ ^
>> ^ ^ ^ ^ = ?
>
Thanks Richard.

Ron