Function - sequence - cast - Mailing list pgsql-general

From Ron St-Pierre
Subject Function - sequence - cast
Date
Msg-id 40AA89DC.2080302@syscor.com
Whole thread Raw
Responses Re: Function - sequence - cast  (Richard Huxton <dev@archonet.com>)
List pgsql-general
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



pgsql-general by date:

Previous
From: "Keith Bottner"
Date:
Subject: Re: Help! Error Compiling
Next
From: Brian Hirt
Date:
Subject: pg_autovacuum seems to be a neat freak and cleans way too much