Function - sequence - cast

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