Function - sequence - cast

Поиск
Список
Период
Сортировка
От Ron St-Pierre
Тема Function - sequence - cast
Дата
Msg-id 40AA89DC.2080302@syscor.com
обсуждение исходный текст
Ответы Re: Function - sequence - cast  (Richard Huxton <dev@archonet.com>)
Список 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 по дате отправления:

Предыдущее
От: "Keith Bottner"
Дата:
Сообщение: Re: Help! Error Compiling
Следующее
От: Brian Hirt
Дата:
Сообщение: pg_autovacuum seems to be a neat freak and cleans way too much