Обсуждение: Function - sequence - cast

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'');

      INSERT INTO address (SELECT strProvince, strAddress  FROM address WHERE addressID = (SELECT addressID FROM
companiesWHERE companyID = (SELECT companyID FROM users WHERE userID=user_id))); 

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

    RETURN 1;

  END '  LANGUAGE 'plpgsql';


It's probably something simple that I'm doing wrong. Can anyone help?

Postgres 7.4.1

Thanks

Ron

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

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