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