Function - sequence - cast

[Ron St-Pierre]

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Richard Huxton]

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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Ron St-Pierre]

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings