Function - sequence - cast

From: Ron St-Pierre <rstpierre(at)syscor(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Function - sequence - cast
Date: 2004-05-18 22:10:36
Message-ID: 40AA89DC.2080302@syscor.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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 companies WHERE 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Brian Hirt 2004-05-19 01:37:11 pg_autovacuum seems to be a neat freak and cleans way too much
Previous Message Taras V. Panchenko 2004-05-18 20:30:43 initdb fails